VBA - On Error Goto (Label)

flipdazed

New Member
Joined
Sep 5, 2011
Messages
25
Hi there,

I'm trying to create a method of preventing people from using my macro on the wrong workbooks and incorrectly named workbooks or sheets.

I have tried:

Code:
[COLOR="Green"]' Error Messages[/COLOR]
ErrMsg11:
    MsgBox ("This macro is intended for 'DT Global Orders Incomplete.xlsx'" & vbCrLf & _
    "Please ensure that is is named correctly or that you are running the macro on the correct file."), , "Filename Error - You got Schooled!"
ErrMsg12:
    MsgBox ("Enter the main sheet is titled:" & vbCrLf & "'DT Global Orders Incomplete'."), , "Wrong Sheet Titles!"

    Windows("DT Global Orders Incomplete.xlsx").Activate
    [COLOR="Blue"]On Error GoTo[/COLOR] ErrMsg11
    Sheets("DT Global Orders Incomplete").Select
    [COLOR="blue"]On Error GoTo[/COLOR] ErrMsg12

It always flags the errors though even if there is no error happening. I tried putting the labels at the end of my sub but then they're never flagged!

Can anyone help me out? Ideally I want a message box to flag up with the message telling someone what they did wrong.

Thanks

:)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Error handlers should appear at the bottom of the sub, and there should be an Exit Sub above them otherwise they will always be executed.
 
Upvote 0
Rearrange your code like this:

Code:
    On Error GoTo ErrMsg11
    Windows("DT Global Orders Incomplete.xlsx").Activate
    On Error GoTo ErrMsg12
    Sheets("DT Global Orders Incomplete").Select
'   Error Messages
ErrMsg11:
    MsgBox ("This macro is intended for 'DT Global Orders Incomplete.xlsx'" & vbCrLf & _
    "Please ensure that is is named correctly or that you are running the macro on the correct file."), , "Filename Error - You got Schooled!"
    Exit Sub
ErrMsg12:
    MsgBox ("Enter the main sheet is titled:" & vbCrLf & "'DT Global Orders Incomplete'."), , "Wrong Sheet Titles!"
    Exit Sub
 
Upvote 0
Thanks guys!

Rory I tried your suggestion but it still didn't flag up correct error messages.

Andrew, thanks for the reply - does the error goto statement define the action for any errors in the following lines then?

Should I add an
Code:
[COLOR="Blue"]On Error Goto [/COLOR]0
after my error tests?
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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