VBA: Yes or No Message Box

February 4th, 2005 | Categories: VBA | Tags: ,

YesNoMessageBoxHere is code to present the user with a Yes/No Message box and have the ability to run code based on the response.

It works by setting a variable equal to the message box response and evaluating the variable with an IF…THEN statement.

The code is commented in locations for changing the message text displayed, where to place code for a Yes response, and No response.

To retrieve a user inputted string, see my post about the InputBox.

Sub YesNoMessageBox()
Dim Answer As String
Dim MyNote As String

    'Place your text here
    MyNote = "Do you agree?"

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

    If Answer = vbNo Then
        'Code for No button Press
        MsgBox "You pressed NO!"
    Else
        'Code for Yes button Press
        MsgBox "You pressed Yes!"
    End If

End Sub

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. February 6th, 2005 at 18:19
    Reply | Quote | #1

    First thing I saw on the graphic above was three question marks, and immediately related that to using non-Roman fonts (as I deal with Korean users/operating systems/file names/dialog boxes on a daily basis).

    Recommend you spell out the question in the MsgBox – at least for the title. I’ve screwed up more than once by agreeing to something I shouldn’t have.

    (I realize that this is a quick demonstration of if…then and not language incompatabilities.)

  2. Mark
    February 7th, 2005 at 11:28
    Reply | Quote | #2

    I use the PRE tag for my code, unfortunately it doesn’t automatically wrap without some CSS which I don’t use.

    I had the message written out but shortened it so it didn’t move my margins into my navigation. However,

    I didn’t give any thought to mutltiple languages and the need to always spell out the message box titles, thanks for the heads up.

  3. MJB
    August 5th, 2008 at 04:45
    Reply | Quote | #3

    Thanks for the useful example! I knew I wanted something like this, but I couldn’t find anything similar in the help files. This yes/no msgbox was exactly what I needed.

  4. REVNED
    August 15th, 2008 at 04:55
    Reply | Quote | #4

    Private Sub Modify_Click()
    x = MsgBox(“Are you sure you want to modify records?”, vbYesNo, “SLSA Engineering Database”)
    DoCmd.OpenForm “Modify Record Menu”, acNormal

    End Sub

    …..i have this code but evertime i click No buttong in the message box it still open me a form… please help me if there is still missing in my codes and what was it?…Im not a programmer, anyone can help with this code?

  5. Matt
    September 15th, 2008 at 21:18
    Reply | Quote | #5

    I would assume that you need an if/then/else loop based on x, otherwise it will hit your second line every time.

  6. SK
    November 10th, 2008 at 19:47
    Reply | Quote | #6

    Sub MsgBox4()

    MsgBox title:=”Analysis of Acctg Activities”, Prompt:=”Can not execute your command. No data availabe for that period.”

    End Sub

    When run this code it jumps to the conclusion. “Wrong number of orgumaents or invalid property assignment” and shows the MsgBox. I ran this code before several times without any problem. Now I am using Excel 2007.

  7. November 13th, 2009 at 18:43
    Reply | Quote | #7

    Thanx for this example!!!

  8. AJ
    February 18th, 2010 at 06:53
    Reply | Quote | #8

    Exactly what I wanted. Simple and effective.
    Thanks a lot

  9. bcole
    March 12th, 2010 at 17:01
    Reply | Quote | #9

    Fantastic! Simple and elegant.
    I plan on inserting into many of my modules to make sure I do not accidentally send the wrong hotkey.
    Thanks!

  10. mahesh
    March 15th, 2010 at 08:40

    Can u write a programm or steps for writting a programm in macros in which there is a formula with variable value “x” such that when you put the value “x” the answer should match exactly to other cell value.
    As i am new and not knowing the code & steps for writting a programm.
    help me out

  11. Nadoj
    August 26th, 2010 at 00:45

    Thanks, that is very nice. It will help stop data errors that have been happening.

  12. August 10th, 2011 at 12:56

    I do not know how to make choices as “Now” or “Later”
    :/