Excel VBA : Message Box

VBA Message Box

The syntax for VBA message box is as follows :

1. Simple VBA Message Box

Sub Msg1()
MsgBox "Hello Everyone"
End Sub

2. Message Box With Title

Sub Msg2()
'Display message box with title "Introduction"
MsgBox "Hello Everyone", , "Introduction"
End Sub

3. Advanced Message Box [1] - Yes and No Buttons

Sub Msg3()

Dim Popup As Integer

Popup = MsgBox("Are you a lazy guy?", vbYesNo, "User's Attitude")

'If user clicks on Yes button then displays a message

If Popup = vbYes Then

MsgBox "Thank you for answering honsestly!!"

Else

'If user clicks on No button then displays a message

MsgBox "Great!"

End If
End Sub

In VBA message box, ‘buttons’ parameter can have any of the following values:

4. Advanced Message Box [2] - Yes, No and Cancel Buttons

Sub Msg4()
Dim popup As Integer
popup = MsgBox("This is the ""Yes/No/Cancel"" popup dialog box", _
vbYesNoCancel, "Select an Option")
If popup = vbYes Then
MsgBox "You may proceed"
ElseIf popup = vbNo Then
MsgBox "Your application is terminated"
End If
End Sub

5. How to pass a value in message box

Sub Msg5()
MsgBox "Your final score is " & Range("A2").Value
End Sub

6. Multi Line VBA Message Box - Adding a Line Break

Add a vbCrLf to insert a line break in VBA message box.

Sub Macro6()
MsgBox "This is Line #1" & vbCrLf & _
"This is Line #2" & vbCrLf & _
vbCrLf & "There are two lines above this one"
End Sub

Note: _ ( space then an Underscore) is a line continuation character. If a statement is too long to fit on a line, it can be continued with _ ( space then an Underscore).

How to use

Open an Excel Workbook
Press Alt+F11 to open VBA Editor
Go to Insert Menu >> Module
Copy the above code and Paste it into module
Save the file as Macro Enabled Workbook (xlsm) or Excel 97-2003 Workbook (xls)
In the code window, press F5 to run the macro

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn

Post Comment 2 Responses to "Excel VBA : Message Box"

AnonymousFebruary 7, 2018 at 8:32 AM
Hi

The 'How to pass a value in a message box' code does not work for me.
This is my code. Where would i add this to add to cell A2?

Sub HowManyEmailsConnor()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")

On Error Resume Next
Set objFolder = objnSpace.Folders("BCML").Folders("Inbox").Folders("Completed Correspondence").Folders("Connor")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If

EmailCount = objFolder.Items.Count
Set objFolder = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

MsgBox "Number of emails in the folder: " & EmailCount, , "Connor Completed Emails"

End Sub

Thank you.
kanchoblindsideMarch 25, 2020 at 10:22 PM
cant save as a macro0free workbook.

This method is stupid