Excel Guides

How to Conditionally Display a Message Box in Excel

The Message Box is a great way to give the user a choice, or display information. It can be used to display a message, ask a question, or get input from the user. The Message Box can be displayed in several ways, including:

  • Yes/No: Displays a message box with Yes and No buttons. The default button is the Yes button.
  • OK/Cancel: Displays a message box with OK and Cancel buttons. The default button is the OK button.
  • Abort/Retry/Ignore: Displays a message box with Abort, Retry, and Ignore buttons. The default button is the Abort button.
  • Yes/No/Cancel: Displays a message box with Yes, No, and Cancel buttons. The default button is the Yes button.
  • Custom: Displays a message box with custom buttons. You can specify up to three buttons and set the default button.

How to Use Message Box Function in Excel

  1. (Optional): If you want to change the title of the Message Box, type a new title after the , vbMsgBoxSetForeground + vbInformation. For example, if you wanted to change it to "My Title" you would type , "My Title" + vbMsgBoxSetForeground + vbInformation. If you do not want to change the title, omit this step.
  2. (Optional): If you want an icon next to your Message Box text, type one of the following after the , vbMsgBoxSetForeground + vbInformation:
    • , vbMsgBoxIconExclamation + vbMsgBoxSetForeground + vbInformation
    • , vbMsgBoxIconQuestion + vbMsgBoxSetForeground + vbInformation
    • , vbMsgBoxIconAsterisk + vbMsgBoxSetForeground + vbInformation
    • (etc.)

    If you do not want an icon next to your text, omit this step.
    (See Step 6 for how to add an icon that is not listed above.)
    (You can also add more than one icon by separating each icon constant with a comma (e.g., ,vbMsgBoxIconExclamation+vbMsgBoxIconQuestion+vbMsgBoxSetForeground+vbInformation). This will display both icons next to your text.)
    (Note: )If you are using Excel 2007 or 2010 and you want your Message Box to have an Icon from another program (e.g., Word or PowerPoint), you must first save that Icon as a .ico file and then follow these instructions:
    1. Right click on Sheet1 tab (at bottom of screen) and select View Code - this opens the Visual Basic Editor (VBE).
      Click on Tools --> References in the menu at top of screen - this opens the References dialog box.
      In "Available References" list, scroll down and check Microsoft Forms 2.0 Object Library - if it's not already checked (see screenshot below). Then click OK.

      Click on Insert --> Userform in menu at top of screen (or press Alt+F11) - this inserts a Userform into your workbook.
      With Userform1 selected (click on it if it's not already selected), click on View --> Code in menu at top of screen (or press F7) - this opens the code window for Userform1.
      In code window, paste this code (change path and filename as needed):
                Private Sub UserForm_Initialize()     Dim sh As Shape     Set sh = Me.Shapes.AddShape(msoShapeRectangle, 5, 5, 32, 32)     sh.Fill.Visible = msoFalse     sh.Line.Visible = msoFalse     sh.Picture.Filename = "C:\Documents\MyPic32x32."  End Sub  
                            " If you do not want an icon next to your text

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.