Changing Worrksheet after Err 1004

MikeG

Well-known Member
Joined
Jul 4, 2004
Messages
816
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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
816
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

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