Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

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.


    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


    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


    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


    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


    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, _
    End Sub


    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, _
    End Sub


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

    Use VBA MsgBox Function In Excel With vbCritical

    MsgBox Prompt:="This is critical", _
    Buttons:=vbCritical, _
    End Sub

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


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


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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, _
    End Sub


    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", _
    End Sub


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

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


    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, _
    End Sub


    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, _
    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
    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