Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

A Ultimate Guide To Learn About VBA MsgBox Function

vba msgbox function in excel

Here is your ultimate guide to master VBA Msgbox Function.

You can use VBA Msgbox Function in excel to get a simple popup box on your excel’s window.

If you really think about VBA MsgBox Function, it has two main roles in your VBA.

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

If you frequently write codes in Excel’s VB editor, I am sure you are familiar with this useful informational tool.

Most of the VBA programmers use VBA MsgBox in their macros to make them more interactive.

If you are not so familiar with VBA MsgBox Function, I bet after reading this post you will fall in love with this function.

Table of Content

  1. Syntax For VBA MsgBox
  2. Customize VBA MsgBox
  3. Examples For VBA MsgBox
  4. Download Sample File
  5. Bottom Line

Syntax For VBA MsgBox

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

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. [Required]

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

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

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. [Optional]

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

Use VBA MsgBox Function In Excel

Customize VBA MsgBox

You can easily customize msg box to with the available options. Let’s have a look at these options.

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.

Use VBA MsgBox Function In Excel with vbOKOnly

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

vbOKCancel

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

Use VBA MsgBox Function In Excel with vbOKCancel

 

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

vbAbortRetryIgnore

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

Use VBA MsgBox Function In Excel with vbAbortRetryIgnore

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

vbYesNoCancel

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

Use VBA MsgBox Function In Excel With vbYesNoCancel

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

vbYesNo

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

Use VBA MsgBox Function In Excel With vbYesNo

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

vbRetryCancel

Now, this will show two buttons, retry & cancel. You can use this to ask the user to retry the operation or to cancel it.

Use VBA MsgBox Function In Excel With vbRetryCancel

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

vbCritical

This constant will show an icon in the msg box, stating that message is critical.

Use VBA MsgBox Function In Excel With vbCritical

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

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

VBA-MSGBOX-9

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

vbExclamation

This constant will show an exclamation icon with the message.

Use VBA MsgBox Function In Excel With vbExclamation

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

vbInformation

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

Use VBA MsgBox Function In Excel With vbInformation

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

vbDefaultButton1

Use this constant to specify the first button of your msg box as the default button.

Use VBA MsgBox Function In Excel With

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

vbDefaultButton2

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

Use VBA MsgBox Function In Excel With vbDefaultButton2

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

vbDefaultButton3

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

Use VBA MsgBox Function In Excel With vbDefaultButton3

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

vbDefaultButton4

Use this constant to specify the fourth button of your msgbox as the default button.

Use VBA MsgBox Function In Excel With vbDefaultButton4

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

vbAppliactionModal

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

Use VBA MsgBox Function In Excel With vbAppliactionModal

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

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

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.

Use VBA MsgBox Function In Excel With vbMsgBoxHelpButton

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

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

vbMsgBoxRight

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

Use VBA MsgBox Function In Excel With vbMsgBoxRight

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

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

Whenever a user responds to the msg box by clicking any of the buttons, a number is generated. This will help you to identify which button is clicked by the user.

Add new line with vbCrLf or vbNewLine

Now if you want to add a new line in the message, you can use vbCrLf or vbNewLine to do this. You just have to put this before the text from where you want  to start the new line.

You can also use character codes Chr(13) & Chr(10) to enter a new line in you msg box.

Use VBA MsgBox Function In Excel With vbCrLf or vbNewLine

vbTab to add a tab in msgbox

And if you want to create a create a tab into the message box, you can use vbTab to do this.

Use VBA MsgBox Function In Excel to create tab in msgbox

Real Life Examples For VBA MsgBox

Here I have listed some real life examples for VBA Msgbox function. I am sure these examples will inspire you to use msg box in your coding.

So let’s get started.

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

Use VBA MsgBox Function In Excel TO Save a File

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

Insert a table into a MsgBox

You can use vbTab to enter a tabular data in the message box.

Use VBA MsgBox Function In Excel To Insert a Table

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

Show message box to the user on opening the 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 msg box 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 Using help in Your Applications on his website excelguru.ca

Download Sample File

Bottom Line

I hope you are now a master of VBA Msgbox function.

I am sure it will help you in creating some awesome codes in excel.

If you are already using this VBA msgbox, please share with me the best code you have written using it.

And, if you are not using it, you can write your first using VBA Msgbox code and share with.

So, now it’s your turn.

Please share this tip with your buddies on Facebook | Twitter | Google+ | LinkedIn

More Tips