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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Well,

It looks like a simple "On Error Resume Next" did it for me.... good enough.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,817
Office Version
  1. 365
Platform
  1. Windows
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, NBVC,

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

kind regards,
Erik
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,817
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Thanks a lot guys!

I will look into playing with those options tomorrow.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

NBVC

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,817
Office Version
  1. 365
Platform
  1. Windows
NBVC,

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

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,541
Members
410,547
Latest member
htran4
Top