How to Use VBA MsgBox Function in Excel

The first thing which I have learned about in VBA was using a message box.

You can also call it msgbox which is its actual syntax. It’s like a simple popup box on your window.

If you think about a msgbox, it has two main usages in VBA as a function.

  • Deliver a message to the user.
  • Get a simple response from the user.

Most of the VBA programmers use it in their macro codes to make them more interactive.

If you are not familiar with it, I bet after reading this post you will fall in love with this function.

Here in this post, you will learn about how to use a msgbox in VBA, how to customize it and some of its real life examples.

So let’s get started.

Syntax

MsgBox(prompt[, buttons] [, title] [, helpfile, context])
syntax vba msgbox function

prompt A string expression used for displaying a message in msg box. The maximum length of characters is 1024, which depends on the width of the characters.

[buttons] You can use this argument to specify buttons, icon style, button’s identity & modality of the message box.

[title] You can use this argument to specify a title text for your msg box.

[helpfile] This argument will help you to specify a help file for the user. The user can access that help file using help button. If you specify a help file, this mandatory to specify a context number.

[context] A numeric expression that uses to assign a help topic from the help menu.

Arguments above which are in square brackets are optional.

Customize

You can easily customize a msgbox with all the available options. So, let’s have a look at these options.

1. vbOKOnly

This will give you a simple OK button.

The user can click on it after reading msg box to close it. You can also use further a macro to execute once the OK button is clicked.

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

2. vbOKCancel

This constant will give you an OK and cancel button. Now, the user has two options to press OK to proceed or cancel to exit.

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

3. vbAbortRetryIgnore

This constant will allow you to show three buttons for About, Retry and Ignore. The user can abort the operation, can retry or can ignore it.

Sub AbortRetryIgnore()
MsgBox Prompt:="You have an error", _
Buttons:=vbAbortRetryIgnore, _
Title:="MsgBox", _
End Sub

4. vbYesNoCancel

This constant shows three buttons for Yes, No and Cancel. The user can Yes to accept and& proceed, No to reject or cancel to close the msgbox.

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 will show 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 will show an icon in the msg box, stating that message is critical.

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

8. vbQuestion

This constant can use when you asking a question to the user.

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 an 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 msg box as the default button.

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 msgbox 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 msgbox as the default button.

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

15. vbAppliactionModal

This constant will suspend the application (Excel), User has to respond to the msgbox to use application.

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

16. vbSystemModal

This constant will suspend all the applications in you OS, User has to respond to the msg box first.

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

17. vbMsgBoxHelpButton

Use this constant to add a help button to you msg box. You can add a help file & a context number to use the help button.

Once the user clicks on the help button, a help menu will appear.

Sub HelpButton()
MsgBox Prompt:="Use Help Button", _
Buttons:=vbOK + vbMsgBoxHelpButton, _
Title:="MsgBox", _
HelpFile:="C:UsersPuneet GogiaDesktopsamplehelp.chm", _
Context:=101
End Sub

18. vbMsgBoxSetForeground

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

Sub SetForeground()
MsgBox Prompt:="This MsgBox is Foreground", _
Buttons:=vbOK + 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:=vbOK + vbMsgBoxRight, _
Title:="MsgBox"
End Sub

20. vbMsgBoxRtlReading

By using this constant, the message box will flip to the right. This constant is mainly designed for Hebrew and Arabic systems.

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

Returning Values

Whenever a user responds to the msgbox by clicking any of the buttons, a number is generated.

This will help you to identify which button is clicked by the user.

Constant

Value

Description

vbOK

1

OK

vbCancel

2

Cancel

vbAbort

3

Abort

vbRetry

4

Retry

vbIgnore

5

Ignore

vbYes

6

Yes

vbNo

7

No

Examples

Here I have listed some real life examples for VBA msgbox function. I am sure these examples will inspire you to use it

1. Run Macro With a VBA MsgBox

Now with the help of msgbox, you can ask the user before running a macro. Let’s look 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 Sub
save workbook with vba msgbox

You can ask the user to save the workbook. And, if the user clicks on the OK button this macro will save the workbook.

2. Insert a table into a MsgBox

You can use vbTab to enter a tabular data in the message box. In this example, 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 cMsg = 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

  • You can’t add four buttons in an msgbox other than you are using help button as a fourth button.
  • For creating a help file for your message box, you can refer to Ken’s post on his website.

Sample File

Download this sample file from here to follow along.

  • Thanks for the MsgBox refresher Puneet! When I discovered VBA (must have been 2000 or 2001) the MsgBox was one of the first things I played around with. It’s great for sending custom messages to Excel users. Relatively easy to pick up a value from a cell, or display record count or timestamp.
    Cheers,
    Kevin

    • Puneet Gogia

      Hello Kevin, Its nice to see you on ExcelChamps

      One More thing that we need to take care about msgbox is to use it in a proper sense. If you we it in access it will give the user an irritation.

      Thanks
      Puneet G

  • Pingback: How To Add Developer Tab To The Excel Ribbon - ExcelChamps()

  • Inet Kemp

    Awesome

    • Puneet Gogia

      Thanks Inet

  • Pingback: How To Create an Table of Content In Excel By Using VBA()

  • Pingback: Text-To-Speech in Excel Will Give Voice To Excel To Speak Text()

  • Vishesh Kumar

    Punit Sir, can you post some basic skill to learn VBA for MS Excel.

  • Krish

    Hi Puneet,

    Thank you for your KT. I tried to run below block of code which copied from your blog. But I’m getting “run-time error”.

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

    Please let me know the why am getting error.