Using Message Box (MsgBox) in Excel VBA

MsgBox function in VBA displays a message in a window and waits for click on a button.

Example of using Yes-No Message Box:

Sub MessageBoxExample()
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
    ' Promt
    strPrompt = "Ask Your Question Here, OK?"
    ' Dialog's Title
    strTitle = "My Tite"
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "NO!"
        MsgBox "Yes!"
    End If
End Sub

Message Box returns an integer value to indicate clicked button (Yes, No, Cancel, etc.):

Return Values
1 OK vbOk
2 Cancel vbCancel
3 Abort vbAbort
4 Retry vbRetry
5 Ignore vbIgnore
6 Yes vbYes
7 No vbNo

You can specify the number and type of buttons of Message Box (the default value for buttons is 0 – display OK button only):

Type of Buttons
0 vbOKOnly OK button only
1 vbOKCancel OK and Cancel buttons
2 vbAbortRetryIgnore Abort, Retry, and Ignore buttons
3 vbYesNoCancel Yes, No, and Cancel buttons
4 vbYesNo Yes and No buttons
5 vbRetryCancel Retry and Cancel buttons


Related articles: