How to Use VBA MsgBox Function in Excel

The first thing which I have learned about VBA was using a message box (“msgbox function”).

It’s like a simple popup box on your Excel window to show a specific message.

If you think about it, you can use it in two ways.

  • 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 and if you are not familiar with it, I bet after reading this post you will fall in love with it.

Expert Tip: If you are a VBA newbie then learning about VBA message box can be a good idea.

So today in this post, I'd like to share with you all the details about using a message box in Excel and some of the real life examples.

...let's get started.

Get a "Cheat Sheet" with 100 Macro Codes [Ready to use]

Syntax

=MSGBOX(prompt, [buttons], [title], [helpfile, context])

syntax vba msgbox function

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

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

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

4. [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.

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

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

How to Customize a Message Box and use Different Buttons

You can easily customize a VBA message box with all the available options.

...let’s have a look at these options.

#1. vbOKOnly

This gives you a simple OK button. User can click on it after reading message 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 gives 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 allows you to show three buttons for About, Retry and Ignore. The user can abort the operation, can retry or can ignore it.

Sub OKCancel()

MsgBox Prompt:="Is this OK", _

Buttons:=vbOKCancel, _

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 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 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 OKOnly()

MsgBox Prompt:="This is a MsgBox ", _

Buttons:=vbOKOnly, _

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 ApplicationModal()
MsgBox Prompt:="This is the Application Modal", _
Buttons:=vbOK + vbApplicationModal, _
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.

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

Once the user clicks on the help button, a help menu 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:=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 message box 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

Real Life Examples to use VBA Message Box Function in Excel

Here I have listed some real life examples for 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 msgbox function, you can ask a 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 click on the OK button the macro code will save the workbook.

#2. Insert a Table into a Message Box

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

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

Sample File

Conclusion

Message box not only help you to deliver a message but also make a creative impression on the user.

You can give them a message and get an simple response. 

And as I said, if you're new to VBA then learning about using a message box is one of the best thing.

I hope this post helped you get closer to your VBA mastery dream but now tell me one thing.

Do you think MsgBox function is one of the coolest thing 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. 

Content Protection by DMCA.com
2018-11-16T06:13:38+00:00

8 Comments

  1. Mamta bhardwaj 2 Oct, 18 at 9:53 pm - Reply

    Thank you so much its so helpful

  2. Ian Parsons 5 Aug, 18 at 1:24 pm - Reply

    Hi,
    Excellent reading here & good to keep for the future.
    Reference the item #2. Insert a Table into a Message Box i have an issue.
    Ived used your code but changed its range to C1:F17
    The issue i have is that i only see results for C1:D17

    I can supply full code & screen shot if required.

    Many thanks for your time with this & helping others by sharing these codes.

  3. Krish 18 Aug, 17 at 2:20 pm - Reply

    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.

  4. Vishesh Kumar 27 Apr, 17 at 1:45 pm - Reply

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

  5. Inet Kemp 21 Feb, 16 at 6:54 pm - Reply

    Awesome

    • Puneet Gogia 25 Feb, 16 at 2:36 pm - Reply

      Thanks Inet

  6. Kevin Lehrbass 17 Jan, 16 at 1:35 pm - Reply

    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 18 Jan, 16 at 7:47 am - Reply

      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

Leave A Comment