Msg Box With Exit Sub - How to quit application

[Guest]

I would like for the application to stop processing if the user selects
vbCancel. Right now, the application does not exit & continues to bug out
since the Sub stops processing. Thanks for any feedback!

Dim Ans As String

Ans = MsgBox("Data columns must occur in the following order: " & vbNewLine
& vbNewLine & _

"User Name (A)" & vbNewLine & _
"Set of Books (B)" & vbNewLine & _

"Click OK to continue. Click Cancel to correct data.", vbOKCancel +
vbQuestion, "Confirm Column Order")

If Ans = vbCancel Then Exit Sub

 

[Norman Jones]

Hi Alatil,

Try:

'=============>>
Public Sub Tester()
Dim Ans As VbMsgBoxResult

Ans = MsgBox(Prompt:="Data columns must occur in the " _
& "following order: " _
& vbNewLine & vbNewLine _
& "User Name (A)" & vbNewLine _
& "Set of Books (B)" _
& vbNewLine & "Click OK to continue. " _
& "Click Cancel to correct data.", _
Buttons:=vbOKCancel + vbQuestion, _
Title:="Confirm Column Order")
If Ans = vbCancel Then
Exit Sub
Else
'Your subsequent code. e.g.:
MsgBox Prompt:="Stlll processing!"
End If
End Sub
'<<=============

 

[Guest]

To quit the Application:

Change from: If Ans = vbCancel Then Exit Sub

To: If Ans = vbCancel Then
ThisWorkbook.Saved = True
Application.Quit
End If

 

[Guest]

The Application.Quit makes the whole application close. I do not want this to
happen as I want the end user to be able to make changes to the data if they
select the Cancel button.

Again, the following code has the application continue to execute other
subs.... which is bad. I just want it to stop processing without closing down
excel. Any ideas?

If vbCancel then Exit Sub

Best,
ALATl

 

[Guest]

This one was pretty simple, once I found it.

If Ans = vbCancel Then End --Instead of Exit Sub
End If

"Exit Sub immediately exits the Sub procedure in which it appears. Execution
continues with the statement following the statement that called the Sub
procedure."

...... which is why my application continued to execute & bug out.

Best,
ALATL

 

[JE McGimpsey]

Since XL is "the application", as I read the question, your statement is
confusing to me.

What else would run subs but the subs you're calling?

If you want to end the processing of a nested chain of subroutines, you
can use

If Ans = vbCancel Then End

but that won't stop any subs (including event macros) from firing in the
future, depending on how they're initiated.

 

[NickHK]

You are aware of the consequences of using End ?
If not, read the help.

NickHK

 

[Guest]

Hi Nick.... I read the *consequences*....heh..... and tried using Stop
instead. I had the same result as before. The user form unloads but code in
all the following subs continues to execute.

I'm open to any more ideas.

Best,
ALATL

 

[NickHK]

One way is to use Functions instead of subs, then check check the return
value:

Private Sub CommandButton1_Click()
If Routine1 = False Then
Exit Sub
Else
Call Routine2
End If
End Sub

Private Function Routine1() As Boolean
Routine1 = (Rnd() > 0.5)
End Function

Private Function Routine2() As Boolean
Routine2 = (Rnd() > 0.5)
End Function

You can also use erorr handling with Err.Raise, as VBA errors "bubble up"

Private Sub CommandButton1_Click()
'Initialte an error handler
On Error GoTo Handler

Call Routine1

Exit Sub
Handler:
Debug.Print Err.Number, Err.Description
End Sub

Private Function Routine1() As Boolean
'No error handler here
Call Routine2
End Function

Private Function Routine2() As Boolean
Dim i As Long
'No error handler here
'Some code that causes an error
i = 1 / 0
End Function

NickHK