VBA – Yes No Message Box (Msgbox)

Associated Files Download Links

This tutorial will cover how to use the VBA MsgBox Function to display messages to users. You might also be interested in our article on InputBoxes.

VBA MsgBox Function

In VBA, it’s easy to display a simple MsgBox:

vba messagebox

 

However you can do a lot more than display a simple OK message box. Let’s quickly look at complicated example before we dive into specifics…

VBA YesNo Message Box

Below we will create a message box with:

  • A title “Message Box Title” and prompt “Text”
  • A question mark icon
  • Yes / No options instead of a simple “OK”
  • Default button = ‘No’

 

vba yesno messagebox

The messagebox will return vbYes or vbNo depending on the user’s choice. You can then then perform different actions based on the choice:

In the next section we will show you all of the options available to you when creating message boxes. Then we will introduce you to the syntax of the MsgBox Function and finally go over other message box examples.

VBA Message Box Options

Take a look at the image below. Here you will see (almost) all of the options available to you when creating message boxes. Take notice of the icons and the different buttons.

vba write messagebox code

This is a screenshot of the “MessageBox Builder” from our Premium VBA Add-in: AutoMacro. The MessageBox Builder allows you to quickly design your desired messagebox and insert the code into your code module. It also contains many other code builders, an extensive VBA code library, and an assortment of coding tools. It’s a must-have for any VBA developer.

Syntax of MsgBox Function

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

prompt (Required) – This is the primary message box text.

buttons – Choose which buttons to display. If omitted, ‘OKonly’. Here you can also specify what icon to show and the default button.

title – The title at the top of the message box. If omitted, the name of the current application is displayed (ex. Microsoft Excel).

helpfile – Specify help file that can be accessed when user clicks on the ‘Help’ button. If specified, then you must also add context (below)

context – Numeric expression representing the Help context number assigned to the appropriate Help topic.

You can probably ignore the helpfile and context arguments. I’ve never seen them used.

Customize Message Box Title and Prompt

The MsgBox function allows you to customize the title and prompt messages like so:

Another example:

vba messagebox okonly

Important! You must remember to surround your text with quotations.

MessageBox LineBreaks

You can also add line breaks to your message box prompts with ‘vbNewLine’.

vba messagebox insert line

Notice we use the & symbol to join text together. You can learn more about using & with text and other options for inserting linebreaks in our article on joining text.

MsgBox Icons

VBA gives you the ability to add one of four pre-built icons to your message boxes:

Icon Constant Icon
vbInformation vba information msgbox icon
vbCritical vba critical msgbox icon
vbQuestion vba question msgbox icon
vbExclamation vba exclamation msgbox icon

The Icon constant should be placed within the button argument:

This will generate the default ‘OK’ message box with the Question icon:

vba msgbox icon

Notice how when you type, the VBA Editor will show you the options available to you:

vba msgbox syntax

This is helpful because you don’t need to remember the exact syntax or names of icons or buttons.

Now we will demo each message box icon:

MsgBox Icons – Information

vba msgbox information

 

MsgBox Icons – Critical

vba critical message box

MsgBox Icons – Question

vba msgbox icon

MsgBox Icons – Exclamation

msgbox exclamation icon

 

Below we will talk about generating message boxes with different button layouts. If you do choose a different message box type, you will need to append the icon type after the buttons using a “+”:

vba messagebox question

 

MsgBox Variables

So far we have worked primarily with the default ‘OK’ message box. The OK message box only has one option: Pressing ‘OK’ allows the code to continue.  However, you can also specify other button groupings: OK / Cancel, Yes / No, etc.

In which case you will want to perform different actions based on which button is pressed.  Let’s look at an example.

Here is the message box we will generate:

vba yes no msgbox

This is the entire code (we will break it down next):

First we assign the messagebox output to an integer variable.

Next we use an If-Else to determine what to do based on which button is pressed:

 

The MsgBox function returns an integer value (between 1-7) so we define the variable as an integer type.  However, instead of referring to the integer number, you can refer to a constant (ex. vbOK, vbCancel, etc.).  Look at this table to see all of the options:

Button Constant Value
OK vbOK 1
Cancel vbCancel 2
Abort vbAbort 3
Retry vbRetry 4
Ignore vbIgnore 5
Yes vbYes 6
No vbNo 7

Now we will demo each button grouping:

OK Message Box – vbOKOnly

messagebox okonly

This is the standard VBA messagebox.

 

OK Cancel Message Box – vbOKCancel

messagebox okcancel

 

Yes No Message Box – vbYesNo

messagebox yes no

 

Yes No Cancel Message Box – vbYesNoCancel

messagebox yes no cancel

 

Abort Retry Ignore Message Box – vbAbortRetryIgnore

messagebox abort retry ignore

 

Retry Cancel Message Box – vbRetryCancel

messagebox retry cancel

 

 

VBA MessageBox Examples

 

Message Box Confirmation Before Running Macro

This code will display a Yes No Message box before calling a macro. If Yes is clicked the macro is called, if No is clicked, the Macro does not run.

vba confirmation box run macro

Yes / No Message Box – Exit Sub

Here we will confirm with the user whether to continue running a macro. If No is clicked, the code will exit the sub, otherwise the procedure will continue.

vba yes no exit sub

You may also like some of this related content...

VBA allows you to choose a file to open using the Application.GetOpenFilename method. In this
Line Break in a Message Box (msgbox) If you need to put a larger amount
VBA InputBox The VBA InputBox allows you to prompt the user to input information.  That
Make Userform Transparent Tek-Tips has a nice post in their forums for making User Forms
Ads