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

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

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

 

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

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

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

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

vbCritical

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

Use VBA MsgBox Function In Excel With vbCritical

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

VBA-MSGBOX-9

vbExclamation

This constant will show an exclamation icon with the message.

Use VBA MsgBox Function In Excel With vbExclamation

vbInformation

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

Use VBA MsgBox Function In Excel With vbInformation

vbDefaultButton1

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

Use VBA MsgBox Function In Excel With

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

vbDefaultButton3

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

Use VBA MsgBox Function In Excel With vbDefaultButton3

vbDefaultButton4

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

Use VBA MsgBox Function In Excel With vbDefaultButton4

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

vbSystemModal

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

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

vbMsgBoxSetForeground

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

vbMsgBoxRight

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

Use VBA MsgBox Function In Excel With vbMsgBoxRight

vbMsgBoxRtlReading

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

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.

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

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.

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