# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  If pressed no to msgbox, then exit sub

## Hari

Hi,

I have included the following 2 lines of code

msgbox ("Running of a Macro empties the UNDO stack. You should save your
workbook with a different version number before running this Macro. Press
Yes if you would you like to exit out of the macro and save your workbook.
Press No if you have already saved the workbook and would like to continue
with macro execution", vbOKCancel, "Warning") As msgboxresult
If msgboxresult = "Cancel" Then Exit Sub


When running the above code it bombs at the line - msgbox ("...") As
msgboxresult
The error im getting is -- compile error : statement invalid outside Type
Block

Please guide me as to what am doing wrong

--
Thanks a lot,
Hari
India

----------


## Edwin Tam

See the following simple example:

Sub testing()
Dim response
response = MsgBox("My message here.", vbYesNo, "My Title")
If response = vbNo Then
Exit Sub
End If
MsgBox ("You clicked YES.")
End Sub

Regards,
Edwin Tam
edwintam@vonixx.com
http://www.vonixx.com


"Hari" wrote:

> Hi,
>
> I have included the following 2 lines of code
>
> msgbox ("Running of a Macro empties the UNDO stack. You should save your
> workbook with a different version number before running this Macro. Press
> Yes if you would you like to exit out of the macro and save your workbook.
> Press No if you have already saved the workbook and would like to continue
> with macro execution", vbOKCancel, "Warning") As msgboxresult
> If msgboxresult = "Cancel" Then Exit Sub
>
>
> When running the above code it bombs at the line - msgbox ("...") As
> msgboxresult
> The error im getting is -- compile error : statement invalid outside Type
> Block
>
> Please guide me as to what am doing wrong
>
> --
> Thanks a lot,
> Hari
> India
>
>
>

----------


## Jim Cone

Hello Hari,

I changed a few things and it works.
Remember "Msgbox" is a function.  If you want a return value
then you enclose the arguments in ( ), just like any other function.
Then you use a variable to store the value returned.
In this case "MsgBoxResult" is assigned the value of the button clicked.
The VBA help file is pretty good explaining messages boxes, it should help.
'-------------------------
Sub Test()
Dim MsgBoxResult As Long

MsgBoxResult = MsgBox("Running a Macro empties the UNDO stack." & vbCr & _
"You should save your workbook with a different version number before running this Macro." & vbCr & _
"Press Yes if you would like to exit out of the macro and save your workbook." & vbCr & _
"Press No if you have already saved the workbook and would like to continue with macro execution.  ", _
vbYesNoCancel, " Warning")

If MsgBoxResult = vbCancel Then
Exit Sub
ElseIf MsgBoxResult = vbYes Then
'do something
Else
'do something else
End If

End Sub
'---------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Hari" <excel_hari@hotmail.com> wrote in message
news:uVkr0yF$EHA.3616@TK2MSFTNGP11.phx.gbl...
> Hi,
> I have included the following 2 lines of code
> msgbox ("Running of a Macro empties the UNDO stack. You should save your
> workbook with a different version number before running this Macro. Press
> Yes if you would you like to exit out of the macro and save your workbook.
> Press No if you have already saved the workbook and would like to continue
> with macro execution", vbOKCancel, "Warning") As msgboxresult
> If msgboxresult = "Cancel" Then Exit Sub
> When running the above code it bombs at the line - msgbox ("...") As
> msgboxresult
> The error im getting is -- compile error : statement invalid outside Type
> Block
> Please guide me as to what am doing wrong
> Thanks a lot,
> Hari
> India

----------


## Patrick Molloy

dim msg as string

msg = "Running of a Macro empties the UNDO stack. You should save your
workbook with a different version number before running this Macro. Press
Yes if you would you like to exit out of the macro and save your workbook.
Press No if you have already saved the workbook and would like to continue
with macro execution"

If MSGBOX( msg , vbOKCancel, "Warning") = vbCancel  Then

Exit Sub
End If



"Hari" wrote:

> Hi,
>
> I have included the following 2 lines of code
>
> msgbox ("Running of a Macro empties the UNDO stack. You should save your
> workbook with a different version number before running this Macro. Press
> Yes if you would you like to exit out of the macro and save your workbook.
> Press No if you have already saved the workbook and would like to continue
> with macro execution", vbOKCancel, "Warning") As msgboxresult
> If msgboxresult = "Cancel" Then Exit Sub
>
>
> When running the above code it bombs at the line - msgbox ("...") As
> msgboxresult
> The error im getting is -- compile error : statement invalid outside Type
> Block
>
> Please guide me as to what am doing wrong
>
> --
> Thanks a lot,
> Hari
> India
>
>
>

----------


## Hari

Hi Edwin, Jim and Patrick,

Thanx a lot for your kind help. All of your codes are working nicely for me.

Jim - Thanx a ton for introducing me to vbcr and msgbox being a function.

One doubt non-related to excel. Please tell me if possible.

IF i check for your responses in Google Im able to see Edwin's reply, but my
Outlook Express is not showing Edwin's reply (Initially it was showing as
message not available in the server etc.) Why is there a difference between
these 2 mediums

Thanks a lot,
Hari
India

"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:ep7p7HG$EHA.1604@TK2MSFTNGP12.phx.gbl...
> Hello Hari,
>
> I changed a few things and it works.
> Remember "Msgbox" is a function.  If you want a return value
> then you enclose the arguments in ( ), just like any other function.
> Then you use a variable to store the value returned.
> In this case "MsgBoxResult" is assigned the value of the button clicked.
> The VBA help file is pretty good explaining messages boxes, it should
help.
> '-------------------------
> Sub Test()
> Dim MsgBoxResult As Long
>
> MsgBoxResult = MsgBox("Running a Macro empties the UNDO stack." & vbCr & _
> "You should save your workbook with a different version number before
running this Macro." & vbCr & _
> "Press Yes if you would like to exit out of the macro and save your
workbook." & vbCr & _
> "Press No if you have already saved the workbook and would like to
continue with macro execution.  ", _
> vbYesNoCancel, " Warning")
>
> If MsgBoxResult = vbCancel Then
>    Exit Sub
> ElseIf MsgBoxResult = vbYes Then
>   'do something
> Else
>   'do something else
> End If
>
> End Sub
> '---------------------------------------
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:uVkr0yF$EHA.3616@TK2MSFTNGP11.phx.gbl...
> > Hi,
> > I have included the following 2 lines of code
> > msgbox ("Running of a Macro empties the UNDO stack. You should save your
> > workbook with a different version number before running this Macro.
Press
> > Yes if you would you like to exit out of the macro and save your
workbook.
> > Press No if you have already saved the workbook and would like to
continue
> > with macro execution", vbOKCancel, "Warning") As msgboxresult
> > If msgboxresult = "Cancel" Then Exit Sub
> > When running the above code it bombs at the line - msgbox ("...") As
> > msgboxresult
> > The error im getting is -- compile error : statement invalid outside
Type
> > Block
> > Please guide me as to what am doing wrong
> > Thanks a lot,
> > Hari
> > India
>

----------


## Jim Cone

Hari,

Re: "Why is there a difference between these 2 mediums"

I can't help you there.

Regards,
Jim Cone

"Hari" <excel_hari@hotmail.com> wrote in message
news:OKUQWgH$EHA.1404@TK2MSFTNGP11.phx.gbl...
> Hi Edwin, Jim and Patrick,
>
> Thanx a lot for your kind help. All of your codes are working nicely for me.
> Jim - Thanx a ton for introducing me to vbcr and msgbox being a function.
> One doubt non-related to excel. Please tell me if possible.
> IF i check for your responses in Google Im able to see Edwin's reply, but my
> Outlook Express is not showing Edwin's reply (Initially it was showing as
> message not available in the server etc.) Why is there a difference between
> these 2 mediums
> Thanks a lot,
> Hari
> India

----------

