Changing Worrksheet after Err 1004

MikeG

Well-known Member
Joined
Jul 4, 2004
Messages
845
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
In my procedure if an error occurs I send to Errortrap.

During execution, the user might reply "No" when asked to replace a file when saving. This generates a 1004 error which is picked up in line 2 below. However, line 3 then causes a Runtime error 9 - subscript out of range error. I know that this line works fine elsewhere to transfer to the Create or Edit a Quote sheet.

Errortrap:
If Err.Number = 1004 Then
Sheets("Create or Edit a Quote").Activate
Endif
Exit Sub

Can anyone help out as to why it is not working here?

Thanks,

Mike
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

MikeG

Well-known Member
Joined
Jul 4, 2004
Messages
845
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Does the worksheet you are trying to activate exist?

Yes, I am sure it exists - I use exactly the same line a couple of places elsewhere in the same sub.

Thanks,

Mike
 

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
Are you sure its *exactly* the same and you don't have a tiny typo in there somewhere?

And if that's not the issue - then I'd guess something else in your other code does something that brings you to a place where excel is failing to recognize that sheet existing...
 
Last edited:

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198

ADVERTISEMENT

And I saw a post you made earlier, which is making me think you are trying to close a new workbook the user chose not to save, and activate a sheet in the original workbook. So my guess is you need an ActiveWorkbook.Close line in there before you attempt to switch back to that sheet (or just need to toggle back to a different workbook)
 

MikeG

Well-known Member
Joined
Jul 4, 2004
Messages
845
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
And I saw a post you made earlier, which is making me think you are trying to close a new workbook the user chose not to save, and activate a sheet in the original workbook. So my guess is you need an ActiveWorkbook.Close line in there before you attempt to switch back to that sheet (or just need to toggle back to a different workbook)

Thanks s hal. I am absolutely certain about the name. And I probably should have combined the threads, but I was on a bit of a different track.

I since did some more web research and found that you can't do the worksheet change after an error trap. But I was able to set a marker and then do Resume Next and then act based on the marker.

I appreciate your help.

MikeG
 

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
Ah, well at least I learned something new too :)

Glad you solved your problem!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,211
Members
430,116
Latest member
d1pakjha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top