Error Handling Code for Worksheet Not Loaded

[NBVC]

Can someone help with an error-handling code for the following please:

I have a custom macro button in the toolbar. If the user clicks that button when there is no worksheet loaded at all (ie. when Excel is just opened), then I would like a message to pop up to alert them of that instead of getting an error message and asking them to debug the code.

The button is linked to a macro in personal.xls

Thanks.

 

[NBVC]

NBVC,

Did you try the error handling solution I proposed? It is pretty straightforward, and is a little more elegant then Resume Next.

Hi jm14,

I was afraid you'd ask that. I wanted to see if Erik's simpler method would have worked....

But I just tried your solution...and it works great! Thanks...

Just because I want to learn this stuff well, why is this considered more elegant than the Resume Next? Is it just because your are trapping a specific error? What if another error occurs other than 1004, then I would have to include these codes obviously in the If statement? Can't I just trap any error and continue on?

How exactly does that "On Error Resume Next" work....If I place it at top of my code, will it avoid any error at any time during the running of the code or only if it happens at the beginning... or do I need to place it at several points within the code where I think an error can occur.?

 

[Joe4]

NBVC,

The way that it works, is that anytime it encounters an error, it will jump to the error checking. If it finds error 1004, it returns the custom message box we set-up. Otherwise it goes to the Else clause in the error checking which will return the error number and description (so you do not have to set up IF clauses for each conceivable error, just the ones you want to "intercept").

The reason why that it is more elegant than Resume Next is because Resume Next will ignore any error instead of just that specific error. That, as you probably know, can be dangerous, as you might be ignoring other errors that you really don't want to ignore, and your code might not function properly.

When you get past the point of where the error might occur in your code, you can get the error checking back to the Excel default with the command:
On Error GoTo 0

So, you will often see code in this format:

On Error GoTo Err_Chk
' Perform steps here
On Error GoTo 0

Let me know if you have any other questions.

 

[NBVC]

Thanks very much, jm14, for this useful information....


I will get this all in one day