VBA MSGBOX – A Complete Guide to Message Box Function + Examples

If MsgBox("Save the workbook?", vbYesNo + vbQuestion, "Save") = vbYes Then
    ThisWorkbook.Save
End If

Syntax

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

• prompt – A string expression to display as the message. The maximum length is approximately 1024 characters, depending on the width of the characters.
• [buttons] – You can use this argument to specify the buttons, the icon style, the default button and the modality of the message box. If you skip it, you get only the OK button.
• [title] – You can use this argument to specify a title text for your message box.
• [helpfile] – This argument will help you to specify a help file for the user. The user can access that help file using the help button. If you specify a help file, it’s mandatory to specify a context number as well.
• [context] – A numeric expression that is the help context number of the help topic to open.

Note: In the above list, the arguments in square brackets are optional, so you can skip them.

How to Customize a Message Box and use Different Buttons

You can easily customize a VBA message box with all the available options. For this, there are multiple constants available to use in the MsgBox. Let’s have a look…

Understanding VBA Constants

1. vbOKOnly

This gives you a simple OK button. The user can click on it after reading the message to close it. You can also run more code once the OK button is clicked.

Sub OKOnly()
MsgBox Prompt:="This is a MsgBox ", _
Buttons:=vbOKOnly, _
Title:="MsgBox"
End Sub

2. vbOKCancel

This constant gives you OK and Cancel buttons. Now the user has two options: press OK to proceed or Cancel to exit.

Sub OKCancel()
MsgBox Prompt:="Is this OK", _
Buttons:=vbOKCancel, _
Title:="MsgBox"
End Sub

3. vbAbortRetryIgnore

This allows you to show three buttons for Abort, Retry and Ignore. The user can abort the operation, can retry or can ignore it.

Sub AbortRetryIgnore()
MsgBox Prompt:="The operation failed", _
Buttons:=vbAbortRetryIgnore, _
Title:="MsgBox"
End Sub

4. vbYesNoCancel

This constant shows three buttons for Yes, No and Cancel. The user can click Yes to accept and proceed, No to reject or Cancel to close the message box.

Sub YesNoCancel()
MsgBox Prompt:="Now, You have three buttons", _
Buttons:=vbYesNoCancel, _
Title:="MsgBox"
End Sub

5. vbYesNo

This constant shows two buttons for Yes and No. The user can click Yes to accept and proceed or No to reject.

Sub YesNo()
MsgBox Prompt:="Now, You have two buttons", _
Buttons:=vbYesNo, _
Title:="MsgBox"
End Sub

6. vbRetryCancel

Now this shows two buttons, Retry and Cancel. You can use this to ask the user to retry the operation or to cancel it.

Sub RetryCancel()
MsgBox Prompt:="Please Retry", _
Buttons:=vbRetryCancel, _
Title:="MsgBox"
End Sub

7. vbCritical

This constant shows the Critical Message icon in the message box, stating that the message is critical.

Sub Critical()
MsgBox Prompt:="This is critical", _
Buttons:=vbCritical, _
Title:="MsgBox"
End Sub

8. vbQuestion

You can use this constant when you are asking the user a question.

Sub Question()
MsgBox Prompt:="What to do now?", _
Buttons:=vbQuestion, _
Title:="MsgBox"
End Sub

9. vbExclamation

This constant will show an exclamation icon with the message.

Sub Exclamation()
MsgBox Prompt:="What to do now?", _
Buttons:=vbExclamation, _
Title:="MsgBox"
End Sub

10. vbInformation

It will show an icon stating that the message is for information.

Sub Information()
MsgBox Prompt:="What to do now?", _
Buttons:=vbInformation, _
Title:="MsgBox"
End Sub

11. vbDefaultButton1

Use this constant to specify the first button of your message box as the default button. The default button is the one that gets clicked when the user presses Enter.

Sub DefaultButton1()
MsgBox Prompt:="Button1 is Highlighted?", _
Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton1, _
Title:="MsgBox"
End Sub

12. vbDefaultButton2

Use this constant to specify the second button of your message box as the default button.

Sub DefaultButton2()
MsgBox Prompt:="Button2 is Highlighted?", _
Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton2, _
Title:="MsgBox"
End Sub

13. vbDefaultButton3

Use this constant to specify the third button of your message box as the default button.

Sub DefaultButton3()
MsgBox Prompt:="Button3 is Highlighted?", _
Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton3, _
Title:="MsgBox"
End Sub

14. vbDefaultButton4

Use this constant to specify the fourth button of your message box as the default button. The fourth button is only there when you add the Help button with vbMsgBoxHelpButton.

Sub DefaultButton4()
MsgBox Prompt:="Button4 is Highlighted?", _
Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton4, _
Title:="MsgBox"
End Sub

15. vbApplicationModal

This constant makes the message box application modal: the user has to respond to the message box before they can continue working in Excel. It’s also the default when you don’t specify a modality.

Sub ApplicationModal()
MsgBox Prompt:="This is the Application Modal", _
Buttons:=vbOKOnly + vbApplicationModal, _
Title:="MsgBox"
End Sub

16. vbSystemModal

This constant makes the message box system modal: all applications are suspended until the user responds to the message box.

Sub SystemModal()
MsgBox Prompt:="This is the System Modal", _
Buttons:=vbOKOnly + vbSystemModal, _
Title:="MsgBox"
End Sub

17. vbMsgBoxHelpButton

Use this constant to add a Help button to your message box. You can add a help file & a context number to use the Help button.

Sub HelpButton()
MsgBox Prompt:="Use Help Button", _
Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
Title:="MsgBox", _
HelpFile:="C:\Users\Puneet Gogia\Desktop\samplehelp.chm", _
Context:=101
End Sub

Once the user clicks on the Help button, the help topic from the help file will appear.

18. vbMsgBoxSetForeground

This constant will help you make your message box window a foreground window.

Sub SetForeground()
MsgBox Prompt:="This MsgBox is Foreground", _
Buttons:=vbOKOnly + vbMsgBoxSetForeground, _
Title:="MsgBox"
End Sub

19. vbMsgBoxRight

The text will be aligned to the right by using this constant.

Sub MsgBoxRight()
MsgBox Prompt:="Text Is In Right", _
Buttons:=vbOKOnly + vbMsgBoxRight, _
Title:="MsgBox"
End Sub

20. vbMsgBoxRtlReading

By using this constant, the text is shown in right-to-left reading order, so the message box looks flipped. This constant is mainly designed for Hebrew and Arabic systems.

Sub MsgBoxRtlReading()
MsgBox Prompt:="This Box is Flipped", _
Buttons:=vbOKOnly + vbMsgBoxRtlReading, _
Title:="MsgBox"
End Sub

Note that vbOK is a return value (see the table below), not a button constant. Its value is 1, the same as vbOKCancel, so Buttons:=vbOK shows OK and Cancel buttons. That’s why the codes above use vbOKOnly for a single OK button.

Returning Values

Whenever a user responds to the message box by clicking any of the buttons, MsgBox returns a number. This helps you to identify which button the user clicked.

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

Real Life Examples to use VBA Message Box Function in Excel

Here I have listed some real-life examples of the VBA message box, and I’m sure these examples will inspire you to use it.

1. Run Macro with a VBA MsgBox

Now, with the help of the MsgBox function, you can ask a user before running a macro. Let’s look at the below macro to understand.

Sub SaveThis()
Dim Result As Integer
Result = MsgBox("Do you want to save this file?", vbOKCancel)
If Result = vbOK Then
ActiveWorkbook.Save
End If
End Sub

You can ask the user to save the workbook and if the user clicks on the OK button the macro code will save the workbook.

2. Insert a Table into a Message Box

You can use vbTab to show tabular data in the message box. In this example, the table is starting from cell A1.

Sub AddToMsgBox()
Dim Msg As String
Dim r As Integer
Dim c As Integer
Dim rc As Integer
Dim cc As Integer
Dim myRows As Range
Dim myColumns As Range
Msg = ""
Set myRows = Range("A:A")
Set myColumns = Range("1:1")
rc = Application.CountA(myRows)
cc = Application.CountA(myColumns)
For r = 1 To rc
For c = 1 To cc
Msg = Msg & Cells(r, c).Text
If c < cc Then Msg = Msg & vbTab
Next c
Msg = Msg & vbCrLf
Next r
MsgBox Msg
End Sub

3. Show Message Box on Opening a File

If you look at the below macro, I have used auto_open to create a message to show on the opening of the workbook.

Sub auto_open()
MsgBox "Welcome To ExcelChamps & Thanks for downloading this file" _
+ vbNewLine + vbNewLine + "You will discover a detailed explanation about MsgBox Function here." _
+ vbNewLine + vbNewLine + "And, Don't forget to check other cool stuff."
End Sub

Points to Remember

1. You can’t add more than three buttons to a message box, unless you add the Help button as the fourth one.
2. For creating a help file for your message box, you can refer to Ken’s post.

Combine Buttons, Icons and a Response

You can add the constants together with a plus sign to get buttons, an icon and a default button in one message box. Then store the result in a variable and use Select Case to act on the button the user clicked. To show the message on more than one line, use vbNewLine between the parts of the text:

Sub DeleteRowsAsk()
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Do you want to delete the blank rows?" & vbNewLine & _
        "This can't be undone.", _
        vbYesNoCancel + vbQuestion + vbDefaultButton2, "Delete Rows")
    Select Case answer
        Case vbYes: MsgBox "Deleting..."
        Case vbNo: MsgBox "Nothing was deleted."
        Case vbCancel: Exit Sub
    End Select
End Sub

Here, vbDefaultButton2 makes No the default button, which is safer for an action that can’t be undone. And to show a value from a cell or a variable in the message, join it to the text with &, like MsgBox “Total: ” & Range(“B10”).Value.

Sample File

Download this sample file from here to learn more about this.

Conclusion

A message box not only helps you to deliver a message but also makes a creative impression on the user. You can give them a message and get a simple response. 

And as I said, if you’re new to VBA then learning about using a message box is one of the best things. I hope this post helped you get closer to your VBA mastery dream but now tell me one thing.

Do you think the MsgBox function is one of the coolest things in VBA?

Make sure to share your views with me in the comment section; I’d love to hear from you and don’t forget to share this tip with your friends. 

Frequently Asked Questions

Can I change the text on the MsgBox buttons?

No. MsgBox only shows the built-in buttons with Windows’ own captions (OK, Cancel, Yes, No and so on). For your own button text, create a UserForm instead.

Can a message box close by itself after a few seconds?

Not with MsgBox, which waits until the user clicks a button. On Windows, you can use CreateObject(“WScript.Shell”).Popup(“Saved”, 3), which closes after 3 seconds.

What is the difference between MsgBox and InputBox?

MsgBox shows a message and returns which button was clicked. InputBox shows a text box and returns what the user typed.

More on VBA

• Add a New Sheet with Name using VBA
• How to Record a Macro in Excel
• VBA Option Explicit