Changing Worrksheet after Err 1004

[MikeG]

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

 

[s hal]

Does the worksheet you are trying to activate exist?

 

[MikeG]

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]

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...

 

[s hal]

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]

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]

Ah, well at least I learned something new too

Glad you solved your problem!