VBA MsgBox Excel Examples Macros Various Message Box Types options

VBA MsgBox Excel Examples with Various Message Box Types options with Macros codes and syntax for yes no and other advanced popup message to display icons command buttons. VBA MsgBox in Excel is one of the most frequently used functions in VBA Macros and Examples. MessageBox or MsgBox function displays a message, optional icon, and selected set of command buttons in a dialog box. It waits for the user to click a button, and returns an Integer indicating which button the user clicked. Here is the syntax and different kinds of MsgBox in VBA. We will see the different options and usage of msgbox like: yes no syntax, arguments, parameters, yes no default buttons, yes no prompt, yes no example, yes no if, yes no return, yes no answer, yes no access, yes no cancel values, yes no response, yes no cancel, variable string, yes no Button Types, yes no critical, yes no warning, yes no exclamation, yes no question, button caption, button labels, button names, if then and exit sub, help button, without ok button, without buttons, no buttons, variable value, variable text, variable input, buttons and icons, button buttons, hello world, multiple lines, access custom buttons, command button, bold text in, radio buttons, access new line, access carriage return, variable type, variable, error handling, on error goto, error message dialog box, display array, two lines, access multiple lines.


VBA MsgBox in Excel – Syntax:

Here is the synatax of VBA MsgBox or Message Box Function in Excel.

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

MessageBox or MsgBox in Excel VBA Syntax

Where

  1. Prompt: It Contains String expression displayed as the message in the dialog box. The Maximum length of Prompt is 1024 Characters. You can use carriage return Character,If prompt consists more than one line.
  2. buttons:It Contains Numeric value specifying the number and type of buttons to display.The default button value is 0.
  3. title:It Contains String expression displayed in the title bar of the dialog box.

MessageBox or MsgBox in Excel VBA – Example Cases:

Here is a short video to show you message box with different types of options:

Here are the different types of Message Boxes available in Excel VBA. You can click on each link to see the respective examples, Screenshots of output and explanation.

VBA MsgBox arguments

MsgBox will take the following parameters:
‘Prompt: This is the message text which you want to show/prompt
‘Buttons Style: This is the type of message box which you want to show, like Yes No buttons with Information Icon
‘Title: This is the title of the message box window
‘Help File, and Context are the other optional paramerter which we use in very rare
‘Here is the example
MsgBox “Here you can enter text to show”, vbYesNo + vbInformation, “This is Title”
‘The above MsgBox will show you Yes No Type message box with information icon and title.

Excel VBA MsgBox Yes No Syntax

The following is the simple Example on MsgBox Yes No Prompt Type:
MsgBox “This is the example Yes No Syntax”, vbYesNo

VBA MsgBox Yes No If

The following example on vba msgbox yes no if to show the different messages boxes based on the selected option.
If MsgBox(“Do you want to see know the current Time”, vbYesNo) = vbYes Then
MsgBox Format(Now(), “HH:MM:SS AMPM”), vbInformation, “Current Time”
End If

VBA Message Box New line,carriage return, two lines, multiple line

We can use vbCr to split the message box text into a new line, carriage return, two lines or multiple lines.
MsgBox “Hello, This is Line ONE” & vbCr & “This is Line TWO”

VBA MsgBox Yes No Cancel Return

The below example on vba msgbox yes no cancel return to access the response of MsgBox. This will help us to access,store and input the msgbox response or string in variable value. We can use this variable text in the further programming.

Dim msgValue
msgValue = MsgBox(“Hello, Are you a graduate? Choos:” _
& vbCr & “Yes: if you are a graduate” _
& vbCr & “Yes: if you are Not a graduate” _
& vbCr & “Yes: if you are Not Intrested” _
, vbYesNoCancel + vbQuestion)

If msgValue = vbYes Then
MsgBox “You are eligible for applying for this Job”
ElseIf msgValue = vbNo Then
MsgBox “You are NOT eligible for applying for this Job”
ElseIf msgValue = vbCancel Then
MsgBox “No Problems, We will find suitable job for you”
End If

VBA If Then MsgBox and Exit Sub

Some times we may want to ask user to continue further, other wise skip the execution of next program. The below example on VBA if then msgbox and exit sub will help you to do this:

If MsgBox(“Would you like to continue…?”, vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If
You can write the next programming steps here… This will execute if user selects No in the above prompt.

VBA On Error GoTo Message Box for Error Handling

MsgBox is also useful in error handling. We can tell vba on error goto a lable and show message box with error number and description. The below code will execute the code and show the error number and description if there is any run-time error.

On Erro GoTo ErrorHanMsg1
‘Your code goes here….

Exit Sub
‘This comes before End Sub or End Function Statement
ErrorHanMsg1:
MsgBox Err.Number & vbCr & Err.Description

MessageBox or MsgBox in Excel VBA:vbOKOnly

Please find the following code and output. It will Display OK button only. When we click OK button, It will return value 1 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbOKOnly()

'Variable Declaration
Dim OutPut As Integer

'Example of vbOKOnly
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbOKOnly, "Example of vbOKOnly")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbOKCancel

Please find the following code and output. It will Display OK and Cancel buttons. When we click OK button, It will return value 1 as a output.And When we click Cancel button, It will return value 2 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbOKCancel()

'Variable Declaration
Dim OutPut As Integer

'Example of vbOKCancel
OutPut = MsgBox("You are VBA Expert, is it True?", vbOKCancel, "Example of vbOKCancel")

If OutPut = 1 Then
'Output = 1(Ok)
MsgBox "Grate! You are VBA Expert, You can learn Advanced Our VBA!", , "Ok - 1"
Else
'Output = 2(Cancel)
MsgBox "You can Star Learning from Basics!", , "Cancel - 2"
End If

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbAbortRetryIgnore

Please find the following code and output. It will Display Abort, Retry, and Ignore buttons. When we click Abort button, It will return value 3 as a output. When we click Retry button, It will return value 4 as a output.And When we click Ignore button, It will return value 5 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbAbortRetryIgnore()

'Variable Declaration
Dim OutPut As Integer

'Example of vbAbortRetryIgnore
OutPut = MsgBox("The Connection has failed. Do you want to Continue?", vbAbortRetryIgnore, "Example of vbAbortRetryIgnore")

If OutPut = 3 Then
'Output = 1(Abort)
MsgBox "Abort!", , "Abort - 3"
ElseIf OutPut = 4 Then
'Output = 4(Retry)
MsgBox "Retry!", , "Retry - 4"
Else
'Output = 5(Ignore)
MsgBox "Ignore!", , "Ignore - 5"
End If

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbYesNoCancel

Please find the following code and output. It will Display Yes, No, and Cancel buttons. When we click Yes button, It will return value 6 as a output. When we click No button, It will return value 7 as a output.And When we click Cancel button, It will return value 2 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbYesNoCancel()

'Variable Declaration
Dim OutPut As Integer

'Example of vbYesNoCancel
OutPut = MsgBox("File already exists. Do you want to replace?", vbYesNoCancel, "Example of vbYesNoCancel")

If OutPut = 6 Then
'Output = 6(Yes)
MsgBox "Yes!", vbInformation, "Yes - 6"
ElseIf OutPut = 7 Then
'Output = 7(No)
MsgBox "No!", vbInformation, "No - 7"
Else
'Output = 2(Cancel)
MsgBox "Cancel!", vbInformation, "Cancel - 2"
End If

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbYesNo

Please find the following code and output.It will display Display Yes and No buttons. When we click Yes button, It will return value 6 as a output.And, When we click No button, It will return value 7 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbYesNo()

'Variable Declaration
Dim OutPut As Integer

'Example of vbYesNo
OutPut = MsgBox("Do you want to replace the existing file?", vbYesNo, "Example of vbYesNo")

If OutPut = 6 Then
'Output = 6(Yes)
MsgBox "Yes! Replace the file", vbInformation, "Yes - 6"
Else
'Output = 7(No)
MsgBox "No! Don't replace the file", , "No - 7"
End If

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbRetryCancel

Please find the following code and output. It will Display Retry and Cancel buttons.When we click Retry button, It will return value 4 as a output.And, When we click Cancel button, It will return value 2 as a output.

Code:

[code language="vb"]
Sub MessageBox_vbRetryCancel()

'Variable Declaration
Dim OutPut As Integer

'Example of vbRetryCancel
OutPut = MsgBox("Close the File.Try Again?", vbRetryCancel + vbDefaultButton2, "Example of vbRetryCancel")

If OutPut = 4 Then
'Output = 4(Retry)
MsgBox "Retry!", , "Retry - 4"
Else
'Output = 2(Cancel)
MsgBox "Cancel It!", , "Cancel - 2"
End If

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbCritical

Please find the following code and output. When we click Ok button, It will return value 1 as a output. And, It will display critical Message Icon.


Code:

[code language="vb"]
Sub MessageBox_vbCritical()

'Variable Declaration
Dim OutPut As Integer

'Example of vbCritical
OutPut = MsgBox("Please enter valid Number!", vbCritical, "Example of vbCritical")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbQuestion

Please find the following code and output.When we click Ok button, It will return value 1 as a output. And, It will display Warning Query icon.

Code:

[code language="vb"]
Sub MessageBox_vbQuestion()

'Variable Declaration
Dim OutPut As Integer

'Example of vbQuestion
OutPut = MsgBox("Are you fresher?", vbQuestion, "Example of vbQuestion")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbExclamation

Please find the following code and output.When we click Ok button, It will return value 1 as a output. And, It will display Warning Message icon.

Code:

[code language="vb"]
Sub MessageBox_vbExclamation()

'Variable Declaration
Dim OutPut As Integer

'Example of vbExclamation
OutPut = MsgBox("Input Data is not valid!", vbExclamation, "Example of vbExclamation")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbInformation

Please find the following code and output.When we click Ok button, It will return value 1 as a output. And, It will display Information Message icon.

Code:

[code language="vb"]
Sub MessageBox_vbInformation()

'Variable Declaration
Dim OutPut As Integer

'Example of vbInformation
OutPut = MsgBox("Succesessfully Completed the Task.", vbInformation, "Example of vbInformation")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbDefaultButton1

Please find the following code and output. By Default it will focus on first (Retry) Button. When we press enter it will result the value of Retry button as 4.

Code:

[code language="vb"]
Sub MessageBox_vbDefaultButton1()

'Variable Declaration
Dim OutPut As Integer

'Example of vbDefaultButton1
OutPut = MsgBox("Close the File.Try Again?", vbRetryCancel + vbDefaultButton1, "Example of vbDefaultButton1")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbDefaultButton2

Please find the following code and output.By Default it will focus on Second(Cancel) Button. When we press enter it will result the value of Retry button as 2.

Code:

[code language="vb"]
Sub MessageBox_vbDefaultButton2()

'Variable Declaration
Dim OutPut As Integer

'Example of vbDefaultButton2
OutPut = MsgBox("Close the File.Try Again?", vbRetryCancel + vbDefaultButton2, "Example of vbDefaultButton2")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbDefaultButton3

Please find the following code and output.By Default it will focus on Third(Cancel) Button. When we press enter it will result the value of Retry button as 2.

Code:

[code language="vb"]
Sub MessageBox_vbDefaultButton3()

'Variable Declaration
Dim OutPut As Integer

'Example of vbDefaultButton2
OutPut = MsgBox("Close the File.Try Again?", vbYesNoCancel + vbDefaultButton3, "Example of vbDefaultButton3")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbApplicationModal

Please find the following code and output.The user must respond to the message box before continuing work in the current application.

Code:

[code language="vb"]
Sub MessageBox_vbApplicationModal()

'Variable Declaration
Dim OutPut As Integer

'Example of vbApplicationModal
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbApplicationModal, "Example of vbApplicationModal")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbSystemModal

Please find the following code and output.All applications are suspended until the user responds to the message box.

Code:

[code language="vb"]
Sub MessageBox_VbMsgBoxSetForeground()

'Variable Declaration
Dim OutPut As Integer

'Example of vbApplicationModal
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbMsgBoxSetForeground, "Example of VbMsgBoxSetForeground")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbMsgBoxHelpButton

Please find the following code and output.Adds Help button to the message box.

Code:

[code language="vb"]
Sub MessageBox_vbMsgBoxHelpButton()

'Variable Declaration
Dim OutPut As Integer

'Example of vbMsgBoxHelpButton
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbMsgBoxHelpButton, "Example of vbMsgBoxHelpButton")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: VbMsgBoxSetForeground

Please find the following code and output.Specifies the message box window as the foreground window.

Code:

[code language="vb"]
Sub MessageBox_VbMsgBoxSetForeground()

'Variable Declaration
Dim OutPut As Integer

'Example of vbApplicationModal
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbMsgBoxSetForeground, "Example of VbMsgBoxSetForeground")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbMsgBoxRight

Please find the following code and output.Here text is right aligned.

Code:

[code language="vb"]
Sub MessageBox_vbMsgBoxRight()

'Variable Declaration
Dim OutPut As Integer

'Example of vbMsgBoxRight
OutPut = MsgBox("Input Data is not valid!", vbMsgBoxRight, "Example of vbMsgBoxRight")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Top

MessageBox or MsgBox in Excel VBA: vbMsgBoxRtlReading

Please find the following code and output.It Specifies text should appear as right-to-left reading on Hebrew and Arabic systems.

Code:

[code language="vb"]
Sub MessageBox_vbMsgBoxRtlReading()

'Variable Declaration
Dim OutPut As Integer

'Example of vbMsgBoxRtlReading
OutPut = MsgBox("Thanks for visiting Analysistabs!", vbMsgBoxRtlReading, "Example of vbMsgBoxRtlReading")

End Sub
[/code]

Output:

MessageBox or MsgBox in Excel VBA

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Double click on ThisWorkbook from Project Explorer
  4. Copy the above code and Paste in the code window
  5. Press F5
  6. You should see the above output

Custom Message Box in Excel VBA:

What if your requirement is not achievable with the available types of MessageBox. You Can create your own MessageBox using Forms in Excel VBA. You can design your own custom MessageBox using Form Controls.
Here is the example Custom MessageBox.
Custom Msgbox in Excel VBA

Top

MessageBox Constants in Excel VBA:

Please find the following table for button argument values:

Constant ValueDescription
vbOKOnly0It Display’s OK button only.
vbOKCancel1It Display’s OK and Cancel buttons.
vbAbortRetryIgnore2It Display’s Abort, Retry, and Ignore buttons.
vbYesNoCancel3It Display’s Yes, No, and Cancel buttons.
vbYesNo4It Display’s Yes and No buttons.
vbRetryCancel5It Display’s Retry and Cancel buttons.
vbCritical16It Display’s Critical Message icon.
vbQuestion32It Display’s Warning Query icon.
vbExclamation48It Display’s Warning Message icon.
vbInformation64It Display’s Information Message icon.
vbDefaultButton10Here first button is default.
vbDefaultButton2256Here second button is default.
vbDefaultButton3512Here third button is default.
vbDefaultButton4768Here fourth button is default.
vbApplicationModal0Application modal. The user must respond to the message box before continuing work in the current application.
vbSystemModal4096System modal. In this case all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton16384Adds Help button to the message box.
VbMsgBoxSetForeground65536Specifies the message box window as the foreground window.
vbMsgBoxRight524288Text is right aligned.
vbMsgBoxRtlReading1048576Specifies text should appear as right-to-left reading on Hebrew and Arabic systems.

 

Top

MessageBox Return Constants and Enumerations in Excel VBA:

ConstantValueDescription
vbOK1OK
vbCancel2Cancel
vbAbort3Abort
vbRetry4Retry
vbIgnore5Ignore
vbYes6Yes
vbNo7No

 

Top

Reference:
MSDN


Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

You may also like...

1 Response

  1. Shady Mohsen says:

    Thanks friend. It helped me a lot.I appreciate your efforts on creating useful VBA codes.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>