MsgBox in Excel VBA
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.
Let’s start with an examples:
Example 1 Simple message MsgBox
Sub MsgBox_Example_1 ()
MsgBox “This is the standard window function MsgBox”
This macro prompts a dialog box:
But the feature that MsgBox offers are much more than just a display of messages that can only accept. So go to its syntax.
Example 2 Two lines 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.
MsgBox “Enjoy” & Chr(10) & “Excel Easy Tutorials 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).
MsgBox “Are you OK today?”, vbYesNo + vbInformation + vbDefaultButton1, “Best friend”
Example 4 Count of sheets MsgBox
MsgBox procedure that informs the names of all subsequent sheets in the active workbook:
Msg = “Here you have Sheets:”
For i = 1 To Sheets.Count
Msg = Msg & Chr(10) & i & “) ” & Sheets(i).Name
MsgBox Msg, vbInformation
The message looks like that:
Example 5 Value from cell
MsgBox can prompts value from particular cell. Sample code is:
MsgBox “Value in A1 cell is ” & Range(“A1”).Value
Example 6 If and MsgBox
What if cell A1 was empty? Use if to prompt proper message.
If Range(“A1”) = “” Then
MsgBox (“Cell A1 is empty”)
MsgBox (“Cell A1 is ” & Range(“A1”))
That were only easiest examples.