Message box wil Yes/No option? (97)

Topic

New Reply

Is it possible to have a message box pop up with a yes or no option?

And can the macro call on a certain other module based on the answer given?

Can the question be something other than yes or no, and if so, can there be more than two options (each calling on a separate macro)?

Thanks in advance. Much appreciated.

Reply | Quote

Replies

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

provides OK, Yes, No, and Cancel options, along with Information symbol, Exclamation symbol, Critical symbol, etc., visual cue options, and user clicks in respons to Yes, No, Cancel can be handled by code or call other routines. See the VBA MsgBox Help for all the options.

However, if you want to go beyond what MsgBox provides you will need to design a Userform to ask your own questions and handle user responses.

Reply | Quote

Here is a demo userorm with yes no maybe as buttons

Steve

Reply | Quote

In Excel VBA help, look up the MsgBox function:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The optional Buttons argument determines what buttons are displayed, and what icon is shown.

For buttons, you can choose between:
vbOKOnly (default; this is used if you omit the Button argument)
vbOKCancel
vbAbortRetryIgnore
vbYesNoCancel
vbYesNo
vbRetryCancel

I hope these are self-explaining. If you need other buttons than these, you will have to create a user form.

The MsgBox function returns a value that indicates which button has been pressed by the user. Possible values are:
vbOK
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo

You can use this as follows:

Dim intResult As Integer
intResult = MsgBox(“Save this result?”, vbYesNoCancel + vbQuestion)
Select Case intResult
Case vbYes
‘ code to save results go here
Case vbNo
‘ code to throw results away go here
Case vbCancel
‘ code to cancel goes here – usually do nothing
End Select

Reply | Quote

Yes (easy), Yes (easy), Yes (hard), and Yes (hard). See the API link in this thread.

Here’s some sample code for the easy Yeses: HTH –Sam

Option Explicit

Sub Macro1()
Dim Rsp As VbMsgBoxResult
    Rsp = MsgBox("Were you good this year?", vbYesNoCancel)
    If Rsp = vbYes Then
        SaidYes
    ElseIf Rsp = vbNo Then
        SaidNo
    End If
End Sub

Sub SaidYes()
    MsgBox "Present"
End Sub

Sub SaidNo()
    MsgBox "Coal"
End Sub

Reply | Quote

Sam,
I tried the sample macro you posted and it failed. To get it to work on my Excel (97) I had to remark out the last part of the Dim statement (see below).
Dim Rsp ‘As VbMsgBoxResult

Maybe you developed under higher level Excel.
It is a handy macro to build on.

I just love this site for all the information that is available here.

Chuck

Reply | Quote

Sorry about that, dim it as Integer as the Help file states. Leave the Option Explicit at the top of the module. It will save you much more time by identifying typing mistakes than it will cost you having to declare all of your variables. Have fun! –Sam

Reply | Quote

You can use

Dim Rsp As Long

in preference to just Dim Rsp, which defaults to a variant.

Andrew C

Reply | Quote