Excel VBA : Message Box

Deepanshu Bhalla 3 Comments ,
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
  1. Open an Excel Workbook
  2. Press Alt+F11 to open VBA Editor
  3. Go to Insert Menu >> Module
  4. Copy the above code and Paste it into module
  5. Save the file as Macro Enabled Workbook (xlsm) or Excel 97-2003 Workbook (xls)  
  6. In the code window, press F5 to run the macro
Related Posts
Spread the Word!
Share
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.

3 Responses to "Excel VBA : Message Box"
  1. 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.

    ReplyDelete
  2. Nice post, keep up with this interesting work. It really is good to know that this topic is being covered also on this web site so cheers for taking time to discuss this! Healthy lunch Ideas

    ReplyDelete
  3. cant save as a macro0free workbook.

    This method is stupid

    ReplyDelete
Next → ← Prev