Error Handling Code for Worksheet Not Loaded

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Well,

It looks like a simple "On Error Resume Next" did it for me.... good enough.
 
Upvote 0
If you want something a little more elegant, you can capture the error like this:
Code:
Sub MyMacro()

    On Error GoTo err_chk
        
'   Enter body of your code below here
''

    Exit Sub

'   Error handling code 
err_chk:
    If Err.Number = 1004 Then
        MsgBox "There is no worksheet open to run the macro on!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If

End Sub

This assumes that the error number you get is '1004'. If it is something else, simply change the value of Err.Number.
 
Upvote 0
Hi, NBVC,

you can use
Code:
Workbooks.Count
for this
it returns zero when no workbooks are open

kind regards,
Erik
 
Upvote 0
Good point Erik, error handling may be overkill, if you can avoid the error altogether!

Still, hopefully my response will provide a good little intro into the world of error handling... :wink:
 
Upvote 0
Thanks a lot guys!

I will look into playing with those options tomorrow.
 
Upvote 0
Hi, NBVC,

you can use
Code:
Workbooks.Count
for this
it returns zero when no workbooks are open

kind regards,
Erik

Hi Erik,

I am most likely doing something wrong...but this didn't work as I expected. Here is the code I tried:
Code:
If Workbooks.Count = 0 Then
    MsgBox "No Spreadsheet Data loaded !" & Chr(10) & Chr(10) & "Please Load Sheet from Purchase Maintenance Module", vbCritical, "No Sheet Loaded"
    Exit Sub
End If

I am getting a run-time error 91 and then it highlights the next line after the code....It seems to completely skip over the Workbooks.Count block.

Do you know why this may be happening?
 
Upvote 0
Hi NBVC

I bet you have a workbook open? Even if it's only your Personal.xls, it still counts. Depending on where your code is located, Workbooks.Count may well not be a useful idea ofr you.

Richard
 
Upvote 0
Hi Richard,

Yeah, the macro is a personal.xls linked to a custom macro button on my toolbar....so it's causing some funny things...especially if i already clicked the button once before.

I think I should probably stick to the "On Errror Resume Next"....that seems to work well enough for me (at least until I find the next problem).....
 
Upvote 0
NBVC,

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

Forum statistics

Threads
1,218,560
Messages
6,143,204
Members
450,469
Latest member
brent3162

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
Back
Top