MsgBox Cancel Button Question

[MJatAflac]

I have a macro I that runs a function that Warns the user that certain
specific information must be in place before continuing. I accomplish this
with a vbOkCancel message box. So if the user clicks ok, I want to continue
running the macro. If they click cancel I want to stop the execution.

I've been writing in VBA for a long time but I've never had the need for
this type of code so I have no clue on how to determine which button they
clicked or how to continue the macro if the user has clicked ok.

I feel like I need to examine the vbMsgBoxresult values but don't know what
value to look for.

Any help as always is greatly appreciated.

 

[tina]

If Msgbox("text here", vbOKCancel + vbDefaultButton2) = vbCancel Then Exit
Sub

the above goes all on one line, regardless of linewrap in this post. add the
line of code to the *beginning* of the event procedure, before the existing
code. so if the sub isn't exited, the rest of the code runs as usual. btw,
you do NOT need to include "+ vbDefaultButton2" in the messagebox code. i'm
in the habit of setting up message boxes so that the when the *default*
button is activated, the result is that nothing happens - the user must make
a concious decision to activate the other button. i do this because so many
people habitually and automatically press Enter when a messagebox pops up -
without reading the text to see what the result will be.

hth

 

[Jim Burke in Novi]

I don't know of any way to halt execution of a macro, though there very well
may be. Is there a reason you are using a macro rather than VBA code? You can
do anything a macro does in VBA code by using DoCmd. Whatever commands you
are executing through the macro, you could instead put them in a function,
using the appropriate DoCmd option. Then in that function, at the point where
you call the function that has the msgbox with the OKCancel, you could return
a boolean value based on the value the user selected form the msgbox, that
tells the macro-replacing function whether to continue or not. I realize
you'd rather just halt execution of the macro and not have to rewrite code,
but it's the only solution I know of. Some of the more expert users may know
of the simpler solution.

 

[fredg]

I have a macro I that runs a function that Warns the user that certain
specific information must be in place before continuing. I accomplish this
with a vbOkCancel message box. So if the user clicks ok, I want to continue
running the macro. If they click cancel I want to stop the execution.

I've been writing in VBA for a long time but I've never had the need for
this type of code so I have no clue on how to determine which button they
clicked or how to continue the macro if the user has clicked ok.

I feel like I need to examine the vbMsgBoxresult values but don't know what
value to look for.

Any help as always is greatly appreciated.

There are two kinds of message boxes uses.
One just gives a message:

MsgBox "This is my message"

The other uses the message box as a function ... MsgBox() .... which
allows you to use the pressed response key to interact with the code:

Using code:

Private Sub YourButton_Click()
If MsgBox("Open the report?",vbOKCancel) = vbCancel
Then
' Cancel Processing here
Exit sub
Else
' Continue with code processing here
DoCmd.OpenReport "ReportName", acViewPreview
End If

 

[Jim Burke in Novi]

Either I misinterpreted your question, or everyone else so far did! It
sounded to me like you have a macro with several commands in it, one of them
somewhere in the middle calling a function. Based on the results of that
function call, you want to stop or continue execution of the macro. If that's
the case, I don' think the answers the others gave will do anything for you,
unless there's something I don't understand about macros and fucntion calls.

 

[fredg]

Either I misinterpreted your question, or everyone else so far did! It
sounded to me like you have a macro with several commands in it, one of them
somewhere in the middle calling a function. Based on the results of that
function call, you want to stop or continue execution of the macro. If that's
the case, I don' think the answers the others gave will do anything for you,
unless there's something I don't understand about macros and fucntion calls.

The original poster refers to a Macro and further down indicates he
has used VBA code. Many posters write Macro when they think VBA code
(like a macro in Excel is).
In any event, all of the replies except yours were in VBA code.

 

[MJatAflac]

I did say macro and that's what I meant. If I were going to be maintaining an
modifying this database I would certainly use VBA code but the database will
be maintained and modified by people who haven't a clue about VBA.

I did get what I needed from all of you though and I very much appreciate it.

I'm going to have one macro that calls the messagebox function. If the user
clicks on OK another macro will be run, if they click cancel the code
execution will stop.

Thanks again for all of your help.

m