How to show Msgbox in Excel Vba

In this article you will learn what is and how to use VBA function – MsgBox.

MsgBox function displays messages on the screen. Depending on the selected parameters it can display other buttons. What you choose affects what actions execute the macro. MsgBox function is very useful because it allows easy communication with user.

Syntax of MsgBox

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

  • prompt – the text you want to see, the only required component of this function
  • buttons – use this to select which buttons you want to see
  • title – the title of the window of your text, if you do not specify the title, is displayed: Microsoft Excel
  • HelpFile – string expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
  • context – Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Examples of MsgBox

Let’s explore some examples:

Example 1 Simple message with MsgBox

Sub MsgBox_Example_1 ()

MsgBox “This is the standard window function MsgBox”

End Sub

This macro prompts a dialog box:

MsgBox Sample Message

But the feature that MsgBox offers are much more than just a display of messages that can only accept.

See also  How to disable close button userform?

Example 2 Two-line message

Message box is displayed with the message in two rows. To get this effect, use a newline character Chr (10) to connect words and use an operator & to connect.

Sub Example2()

MsgBox “Enjoy” & Chr(10) & “Best Excel Tutorial!”

End Sub

MsgBox Enjoy Best Excel Tutorial

Example 3 Information and question MsgBox

Displays a message box with two buttons – Yes and No. There is information icon. The title bar caption “Best Friend”, the default button is the first button (Yes).

Sub Example3()

MsgBox “Are you OK today?”, vbYesNo + vbInformation + vbDefaultButton1, “Best friend”

End Sub

Example 4 Count of sheets with MsgBox

MsgBox procedure that informs the names of all subsequent sheets in the active workbook:

Sub Example4()

Msg = “Here you have Sheets:”

For i = 1 To Sheets.Count

Msg = Msg & Chr(10) & i & “) ” & Sheets(i).Name


MsgBox Msg, vbInformation

End Sub


MsgBox Sample Count Sheets

Example 5 Prompting value from a cell

MsgBox can prompts value from a particular cell.

Sub Example5()

MsgBox “Value in A1 cell is ” & Range(“A1”).Value

End Sub

Example 6 If and MsgBox

What if cell A1 was empty? Use if to prompt proper message.

Sub Example6()

If Range(“A1”) = “” Then

MsgBox (“Cell A1 is empty”)


MsgBox (“Cell A1 is ” & Range(“A1”))

End If

End Sub

MsgBox Cell Empty

This code uses an If statement to check if cell A1 is empty and displays an appropriate message accordingly.

These examples provide a glimpse of the versatility of the MsgBox function in VBA. As you learn more about Excel and VBA, you can explore and utilize this function for various purposes in your macros. Continue your Excel learning journey with Best Excel Tutorial to become an Excel master!