VBA - Exit All Methods, Leave UserForm Running


New Member
Jun 22, 2012
I am running a project from a UserForm. Several methods deep I have an error checking function. Upon receiving an error, I would like to show an msgBox then return to the UserForm without continuing the parent methods. I tried 'End' but this ends the UserForm itself. Could someone point me in the right direction? (I really don't want to have to add booleans to flag errors all the way through my methods)

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Are you using any error handling code? Like "On Error GoTo ErrDBConnection" and stuff like that?
Because imho, that is the way you should be handling this...

Also, if you get an error a few methods deep... I think it's strange to want to skip any further handling in parent methods...
You need a way for the error to 'bubble up' to where you want to handle it. And when you handle it, all parent methods of that location should be able to continue their work, otherwise that error was not really handled.

Basically, the simplest way to achieve what you want, would be like this:
- add a catch-all errorhandler in your userform event handlers (for click handlers etcetera) where you call the next level of code.
- don't use any error handler in the lower levels of code.
- if any error pops up at any depth level , it bubbles up right to the handler in the userform event, where you can show the messagebox and leave it at that.

It might look like this:

Public Sub btnSomeOperation_Click()
   On Error GoTo ErrUnexpected
   Call TheNextLevel
   Exit Sub
   MsgBox "Something unexpected happened, sorry, please try again...", vbOKOnly + vbExclamation
   Resume ExitSub
End Sub
Upvote 0
You can use Err.Raise to pass errors upwards

Sub One()
    On Error GoTo EHandle1
    Call Two(0)
    MsgBox "end of one"
    Exit Sub
    MsgBox "error in one" & vbCr & Error
    Resume Next
End Sub

Sub Two(n As Long)
    On Error GoTo EHandle2
    MsgBox 1 / n
    MsgBox "end of two"
    Exit Sub
    'MsgBox "error in two" & vbCr & Error
    Err.Raise 27 + vbObjectError, Description:="my Error"
End Sub
Upvote 0
I'm not receiving any error, so using an OnError would not work. My Error checking method is checking to make sure the data is of the expected data type from my programming logic. This helps me remove Logic Errors. (i.e. If I ask the user for a number to add to a constant variable, and the user enters a string with no numbers, I wouldn't want to continue the parent methods' Logic with the bad data.) Does this make any sense to anyone besides myself or is this just such an abstract and/or round-about way of testing for proper data input?
Upvote 0
You're talking about basic data validation... imho, that should not be a few methods deep like you described at first...

You can perfectly combine the example I gave with the technique mikerickson gave:

Use my errorhandling template in your first level userform event handlers.
Use the Err.Raise method wherever you feel the need to jump out of your validation and back to the userform method that made the first call down. You don't need to use errorhandling in your validation methods, you can simply raise your own error, which will then bubble upwards to the first errorhandler it finds, which will be the one in the form eventhandler.

I hope that's clear, feel free to ask if it's not...
Upvote 0
Oh! I get it. Unfortunately, this project requires the user to enter data throughout several levels of methods. I didn't realize that using Err.Raise you could 'create your own error.' It makes sense though. Thanks, I got it working.
Upvote 0
Scratch that. I thought it was working.
I added the Err.Raise to my lower functions, and the errorhandling template to my top calling method. It shows my error, but never enters the code under the errorhandling label.

My lowest function Err.Raise statement:
Err.Raise 665 + vbObjectError, Description:="DatSheet is corrupt"

My top calling method:
Private Sub UserForm_Activate()
     On Error GoTo DataSheetCorrupt
     GetData 'my lower method call
     Exit Sub
     If Err.Number = 665 + vbObjectError then
           MsgBox Err.Description
     End If
End Sub

I even tested with the code you gave me (the division by zero) running from excel (not in debug mode), and it doesn't catch the error, it just brings up the run-time error dialog box (and the break in debug).
"Run-Time Error "11" Division by zero" options: End, Debug, or Help

What's going on? The code you gave me should work.
Upvote 0
Well I feel sheepish, I found out what was wrong...
I had 'Break on all Errors' selected instead of 'Break on all Unhandled Errors' in Tools-->Options...-->General-->Error Trapping

Both are working, Ignore my previous post.
Upvote 0
Glad you got it working... That 'Break on all errors' can be a tough one if you don't know about it or forget to look ;)
Upvote 0

Forum statistics

Latest member

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