VBA Message Box (msgbox) – The Message Can Do Better




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

The VBA message box (msgbox) helps convey a pre-defined messages to a user. Developers often use the message box for stuff ranging from debugging vba (msgbox “I am here” types) to prompting users for action (msgbox (“Sorry, what you asked for could not be done”, vbAbortRetryIgnore)). While it remains one of the most useful and frequently used commands in vba, it is also common to find developers being stuck with the most primitive form of the message box and losing out on the opportunity to customize it. In this article we look at how you can enhance the VBA message box and make it more effective.

The basic form of the Message Box (msgbox) in VBA

1
2
3
Sub basic_messagebox()
    MsgBox "Hi there"
End Sub

simple-msgboxTime honored and tested, this is the basic form of the message box. This form is something that anyone who has written more than 5 lines of VBA code is familiar with. This message box simply shows a simple message to the user. All you have to do make this work is to specify the message at the end of the msgbox command in your vba code. However, there are many ways in which this simple message box can be customized.

Customizing the VBA Message Box

Let’s explore a few ways in which we can customize the message box. Before we go further, let’s examine the complete syntax of the message box:

Return value = MsgBox(Prompt, Button and Icon types, Title, Help File, Help File Context)

Where:
Return Value – Indicates the action the user took when the message box was shown to him/her.
Prompt - It is the message contained in the main body of the message box.
Button and Icon Types – This specifies the set of buttons & Icons and their placement as they would appear to the user.
Help File – This is the path to a help file that the user can refer to on this topic.
Help File Context – This is the pointer to that part of the help file that specificially deals with this message.

Let’s take an example that will provide greater clarity.

1
2
3
Sub basic_messagebox()
    i = MsgBox("Do you wish to be terminated ?", vbAbortRetryIgnore, "Greetings Earthlings", "test.hlp", 100)
End Sub

The output of the above piece of code will be something like this:
cutomize-message-box-msgbox-vba

Let’s take each of part of the message box and see how we can customize it.

How to customize the buttons in a VBA message box

Rather than showing the user the plain vanila “Ok” button, you can equip your message box with buttons that make the user feel that he/she is making an intelligent choice.

vba-msgbox-yesno-okonly

The example above exaggerates but only slightly so. It is all common to find developers leaving OK only options for unsuspecting users to click. Since vba provide the ability to catch the return value (the choice made by the user), there’s no reason why any reasonable developer would not want to catch that input and provide the user with greater control over how the program responds.

Here is a list of various buttons and icons you can use in the vba message box.

vba-message-box-msgbox-list

You can even combine two or more buttons (and icons) by simply using the + symbol. In the example given below, we combine the ok button with the critical icon and show the combination to the user. (In addition, You can also specify which button has to have the default focus by using the vbDefaultButton options from the above chart)

1
2
3
Sub basic_messagebox()    
    i = MsgBox("Hi there", vbOKOnly + vbCritical)
End Sub

Capturing the user action from a vba message box

As highlighted in the sections above, the message box provides the ability to capture the user action and take further action based on that feedback. You can store the output of a vba message box in a variable and then take appropriate action based on the user’s choice. Here’s a list of return values associated with the user clicking various buttons in a message box.

vba-message-box-msgbox-return-type-values

Inserting multiple lines in a VBA message box

Suppose you had a long message that you wanted to show in multiple lines. Since the message enclosed in quotes is typically a string, you can use the vba new line character (vbCrLf) to seperate out various parts of a long message into multiple lines. Here is the code:

1
2
3
Sub basic_messagebox()
    MsgBox "This is the first line" & vbCrLf& "This is the second line"
End Sub

And this is how it would appear to the user:
vba-msgbox-in-multiple-lines

Inserting special characters in a VBA message box

Often times, while working with other languages, you may want to insert characters outside of the 26 English characters. In such cases, you can simply use the VBA char function (chr) to insert the special characters.

1
2
3
Sub basic_messagebox()
    MsgBox Chr(200) & "spani" & Chr(244) & "l"
End Sub

In the example above, we have used two special characters to create a string which has non-english alphabets.
special-character-in-vba-msgbox

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. JP wrote:

    I encapsulated the MsgBox function, I like it better than having MsgBox calls littering my code. I posted an example on my blog recently.

  2. Jon Peltier wrote:

    You should show how to get the user’s response:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    Dim Response As VbMsgBoxResult
    Response = MsgBox("Do you wish to be terminated?", vbYesNo + vbQuestion)

    Select Case Response
        Case vbYes
            ' terminate
       Case vbNo
            ' do not terminate
    End Select
  3. Ajay wrote:

    Jon,
    Thanks for providing the implementation.

    JP,
    Taking a cue from what Jon said, if in the below mentioned code, the function call for “MessageBox” were to be replaced with the standard VBA msgbox, it should give the same results.

    [

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub testme1()
    Dim result As Integer
      result = MessageBox("hello", vbInformation + vbYesNo, "My title")
      Select Case result
        Case 1, 4, 5, 6
          MessageBox ("you want to continue!")
        Case 2, 3, 7
          MessageBox ("you want to stop")
      End Select
    End Sub

    … works pretty much the same as…

    [

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub testme2()
    Dim result As Integer
      result = MsgBox("hello", vbInformation + vbYesNo, "My title")
      Select Case result
        Case 1, 4, 5, 6
          MsgBox ("you want to continue!")
        Case 2, 3, 7
          MsgBox ("you want to stop")
      End Select
    End Sub

    Pardon me being naive, did I miss something along the way ?

  4. Chris wrote:

    The purchases I make are eirntely based on these articles.

  5. Sharon wrote:

    Very helpful information. Can you change to color of the message box?

  6. Ricco DeCicco wrote:

    ‘I found the below code which allows custom button text to be
    ‘displayed onto the standard message box.
    ‘ Paste this into a new module in VBA
    ‘———————-
    Option Explicit

    Private Const MB_YESNOCANCEL = &H3&
    Private Const MB_YESNO = &H4&
    Private Const MB_RETRYCANCEL = &H5&
    Private Const MB_OKCANCEL = &H1&
    Private Const MB_OK = &H0&
    Private Const MB_ABORTRETRYIGNORE = &H2&
    Private Const MB_ICONEXCLAMATION = &H30&
    Private Const MB_ICONQUESTION = &H20&
    Private Const MB_ICONASTERISK = &H40&
    Private Const MB_ICONINFORMATION = MB_ICONASTERISK
    Private Const IDOK = 1
    Private Const IDCANCEL = 2
    Private Const IDABORT = 3
    Private Const IDRETRY = 4
    Private Const IDIGNORE = 5
    Private Const IDYES = 6
    Private Const IDNO = 7
    Private Const IDPROMPT = &HFFFF&
    Private Const WH_CBT = 5
    Private Const GWL_HINSTANCE = (-6)
    Private Const HCBT_ACTIVATE = 5
    Private Type MSGBOX_HOOK_PARAMS
    hwndOwner As Long
    hHook As Long
    End Type
    Private MSGHOOK As MSGBOX_HOOK_PARAMS
    Private Declare Function GetCurrentThreadId Lib “kernel32″ () As Long
    Public Declare Function GetDesktopWindow Lib “user32″ () As Long
    Private Declare Function GetWindowLong Lib “user32″ Alias _
    “GetWindowLongA” (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function MessageBox Lib “user32″ Alias _
    “MessageBoxA” (ByVal hwnd As Long, ByVal lpText As String, _
    ByVal lpCaption As String, ByVal wType As Long) As Long
    Private Declare Function SetDlgItemText Lib “user32″ Alias _
    “SetDlgItemTextA” (ByVal hDlg As Long, ByVal nIDDlgItem As Long, _
    ByVal lpString As String) As Long
    Private Declare Function SetWindowsHookEx Lib “user32″ Alias _
    “SetWindowsHookExA” (ByVal idHook As Long, ByVal lpfn As Long, _
    ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function SetWindowText Lib “user32″ Alias _
    “SetWindowTextA” (ByVal hwnd As Long, ByVal lpString As String) As Long
    Private Declare Function UnhookWindowsHookEx Lib “user32″ _
    (ByVal hHook As Long) As Long
    Dim mbFlags As VbMsgBoxStyle
    Dim mbFlags2 As VbMsgBoxStyle
    Dim mTitle As String
    Dim mPrompt As String
    Dim But1 As String
    Dim But2 As String
    Dim But3 As String
    Public Function MessageBoxH(hwndThreadOwner As Long, _
    hwndOwner As Long, mbFlags As VbMsgBoxStyle) As Long
    ‘This function calls the hook
    Dim hInstance As Long
    Dim hThreadId As Long
    hInstance = GetWindowLong(hwndThreadOwner, GWL_HINSTANCE)
    hThreadId = GetCurrentThreadId()
    With MSGHOOK
    .hwndOwner = hwndOwner
    .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, _
    hInstance, hThreadId)
    End With
    MessageBoxH = MessageBox(hwndOwner, Space$(120), Space$(120), mbFlags)
    End Function
    Public Function MsgBoxHookProc(ByVal uMsg As Long, _
    ByVal wParam As Long, ByVal lParam As Long) As Long
    ‘This function catches the messagebox before it opens
    ‘and changes the text of the buttons – then removes the hook
    If uMsg = HCBT_ACTIVATE Then
    SetWindowText wParam, mTitle
    SetDlgItemText wParam, IDPROMPT, mPrompt
    Select Case mbFlags
    Case vbAbortRetryIgnore
    SetDlgItemText wParam, IDABORT, But1
    SetDlgItemText wParam, IDRETRY, But2
    SetDlgItemText wParam, IDIGNORE, But3
    Case vbYesNoCancel
    SetDlgItemText wParam, IDYES, But1
    SetDlgItemText wParam, IDNO, But2
    SetDlgItemText wParam, IDCANCEL, But3
    Case vbOKOnly
    SetDlgItemText wParam, IDOK, But1
    Case vbRetryCancel
    SetDlgItemText wParam, IDRETRY, But1
    SetDlgItemText wParam, IDCANCEL, But2
    Case vbYesNo
    SetDlgItemText wParam, IDYES, But1
    SetDlgItemText wParam, IDNO, But2
    Case vbOKCancel
    SetDlgItemText wParam, IDOK, But1
    SetDlgItemText wParam, IDCANCEL, But2
    End Select
    UnhookWindowsHookEx MSGHOOK.hHook
    End If
    MsgBoxHookProc = False
    End Function

    Public Function BBmsgbox(mhwnd As Long, _
    mMsgbox As VbMsgBoxStyle, Title As String, _
    Prompt As String, Optional mMsgIcon As VbMsgBoxStyle, _
    Optional ButA As String, Optional ButB As String, _
    Optional ButC As String) As String
    ‘This function sets your custom parameters and returns
    ‘which button was pressed as a string
    Dim mReturn As Long
    mbFlags = mMsgbox
    mbFlags2 = mMsgIcon
    mTitle = Title
    mPrompt = Prompt
    But1 = ButA
    But2 = ButB
    But3 = ButC
    mReturn = MessageBoxH(mhwnd, GetDesktopWindow(), _
    mbFlags Or mbFlags2)
    Select Case mReturn
    Case IDABORT
    BBmsgbox = But1
    Case IDRETRY
    BBmsgbox = But2
    Case IDIGNORE
    BBmsgbox = But3
    Case IDYES
    BBmsgbox = But1
    Case IDNO
    BBmsgbox = But2
    Case IDCANCEL
    BBmsgbox = But3
    Case IDOK
    BBmsgbox = But1
    End Select
    End Function

    Sub Test()
    Dim mReturn As String
    mReturn = BBmsgbox(1, vbYesNoCancel, “Hi There”, “Cool ?”, , “Go for it”, “No way”, “Let me think”)
    MsgBox “You pressed ” + mReturn
    End Sub

  7. Robert wrote:

    Nicely done, this was the most concise and easy to understand article I found on how to use message boxes.

  1. Can you get rid of the cancel button from a message box? wrote:

    [...] "Hello", vbOKCancel This LINK will give more info about Messge Box. Regards [...]

  2. VBA code message box dilemma...... wrote:

    [...] and add a little more structure, you can display approx 1024 characters in a message box. This link might give you some ideas. Here is an example of how to use vbcrlf to add a carriage return-line [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel