Create VBA MsgBox Custom Buttons in Excel: 2 Methods

Code Breakdown:

#If VBA7 Then
Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" _
        () As Long

• This line declares a private function “GetCurrentThreadId” that retrieves the thread identifier of the calling thread. This function is defined in the “kernel32” library.

Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" _
 (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As _
 LongPtr, ByVal dwThreadId As Long) _
 As LongPtr

• This part declares a private function “SetWindowsHookEx” that installs an application-defined hook procedure into a hook chain. This function is defined in the “user32” library and has an alias name of “SetWindowsHookExA”.

Private Declare PtrSafe Function SetDlgItemText Lib "user32" _
 Alias "SetDlgItemTextA" _
 (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long, ByVal _
  lpString As String) As Long

• It declares another private function “SetDlgItemText” that sets the text of a control in a dialog box to the specified string. This function is defined in the “user32” library and has an alias name of “SetDlgItemTextA”.

Private Declare PtrSafe Function CallNextHookEx Lib "user32" _
 (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As _
 LongPtr, lParam As Any) _
  As LongPtr

• This line declares a private function “CallNextHookEx” that passes the hook information to the next hook procedure in the current hook chain. This function is defined in the “user32” library.

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" _
 (ByVal hHook As LongPtr) As Long

• This part declares a private function “UnhookWindowsHookEx” that removes a hook procedure installed in a hook chain by the SetWindowsHookEx function. This function is defined in the “user32” library.

Private hHook As LongPtr
#End If

• This line declares a private variable “hHook” of the “LongPtr” data type.

Private Const WH_CBT = 5

• This line declares a constant “WH_CBT” with a value of 5. This constant specifies the type of hook procedure to be installed by the SetWindowsHookEx function.

Private Const HCBT_ACTIVATE = 5

• This line declares a constant “HCBT_ACTIVATE” with a value of 5. This constant specifies that the hook procedure should be called when a window is activated.

Private sMsgBoxDefaultLabel(1 To 7) As String
Private sMsgBoxCustomLabel(1 To 7) As String
Private bMsgBoxCustomInit As Boolean

• These lines declare three private variables. “sMsgBoxDefaultLabel” and “sMsgBoxCustomLabel” are both arrays of strings with seven elements each

Private Sub MsgBoxCustom_Init()
Dim ID As Integer.
Dim vA As Variant
vA = VBA.Array(vbNullString, "OK", "Cancel", "Abort", "Retry", _
"Ignore", "Yes", "No")

• This line initializes the variant variable “vA” with an array containing string values for different button options.

For ID = 1 To 7
sMsgBoxDefaultLabel(ID) = vA(ID)

• It assigns the value of vA(ID) to the corresponding index of an array called “sMsgBoxDefaultLabel”.

sMsgBoxCustomLabel(ID) = sMsgBoxDefaultLabel(ID)

• It assigns the value of the corresponding index of “sMsgBoxDefaultLabel” to the corresponding index of another array called “sMsgBoxCustomLabel”.

Next ID
bMsgBoxCustomInit = True

• This line assigns “True” to a Boolean variable “bMsgBoxCustomInit”.

Public Sub MsgBoxCustom_Set(ByVal ID As Integer, Optional ByVal _
vLabel As Variant)
    If ID = 0 Then Call MsgBoxCustom_Init
    If ID < 1 Or ID > 7 Then Exit Sub
    If Not bMsgBoxCustomInit Then Call MsgBoxCustom_Init
    If IsMissing(vLabel) Then
        sMsgBoxCustomLabel(ID) = sMsgBoxDefaultLabel(ID)
    Else
        sMsgBoxCustomLabel(ID) = CStr(vLabel)
    End If
End Sub

• This is a public sub procedure called “MsgBoxCustom_Set”.
• It takes two arguments, “ID” which is required and represents the index of the custom message box button (ranging from 1 to 7), and an optional argument “vLabel” which represents the new label for the custom button.
• The first conditional statement checks if ID is equal to zero. It calls the “MsgBoxCustom_Init” sub procedure to initialize the custom message box.
• The second conditional statement checks if ID is outside the range of 1 to 7. If true, it exits the sub procedure.
• The third conditional statement checks if the custom message box has been initialized. If not, it calls the “MsgBoxCustom_Init” sub procedure.
• If “vLabel” is not provided, the default label is set to the custom button at the specified index.
• If “vLabel” is provided, the label for the custom button at the specified index is set to the value of “vLabel” as a string.

Public Sub MsgBoxCustom_Reset(ByVal ID As Integer)
    Call MsgBoxCustom_Set(ID)
End Sub

• This VBA code defines a public subroutine called MsgBoxCustom_Reset that takes an integer argument ID.
• The subroutine calls another subroutine called MsgBoxCustom_Set with the same ID argument. This effectively resets the custom message box label for the specified ID to its default value, which is set in the MsgBoxCustom_Init subroutine.

#If VBA7 Then
Private Function MsgBoxCustom_Proc(ByVal lMsg As Long, ByVal wParam As LongPtr, _
ByVal lParam As LongPtr) As LongPtr
#Else
Private Function MsgBoxCustom_Proc(ByVal lMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
#End If
    Dim ID As Integer
    If lMsg = HCBT_ACTIVATE And bMsgBoxCustomInit Then
        For ID = 1 To 7
            SetDlgItemText wParam, ID, sMsgBoxCustomLabel(ID)
        Next ID
    End If
    MsgBoxCustom_Proc = CallNextHookEx(hHook, lMsg, wParam, lParam)
End Function

• This code defines a function called MsgBoxCustom_Proc that is conditionally compiled based on the VBA version being used.
• If VBA7 or higher is being used, the function takes three arguments of type LongPtr (i.e., 64-bit integers on 64-bit systems) and returns a value of type LongPtr. If an earlier version of VBA is being used, the function takes three arguments of type Long (i.e., 32-bit integers on 32-bit and 64-bit systems) and returns a value of type Long.
• The function is a hook procedure when a message is sent to a dialog box. Specifically, the function is designed to intercept the HCBT_ACTIVATE message, sent to a window just before it is activated. When this message is received, and the custom message box has been initialized, the function loops through the seven possible buttons on the message box and sets the text of each button to the custom label that has been specified (if any).
• The function returns the result of calling the CallNextHookEx function, which passes the message to the next hook procedure in the chain.

Public Sub MsgBoxCustom( _
    ByRef vID As Variant, _
    ByVal sPrompt As String, _
    Optional ByVal vButtons As Variant = 0, _
    Optional ByVal vTitle As Variant, _
    Optional ByVal vHelpfile As Variant, _
    Optional ByVal vContext As Variant = 0)
    hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxCustom_Proc, 0, _
    GetCurrentThreadId)
    If IsMissing(vHelpfile) And IsMissing(vTitle) Then
        vID = MsgBox(sPrompt, vButtons)
    ElseIf IsMissing(vHelpfile) Then
        vID = MsgBox(sPrompt, vButtons, vTitle)
    ElseIf IsMissing(vTitle) Then
        vID = MsgBox(sPrompt, vButtons, , vHelpfile, vContext)
    Else
        vID = MsgBox(sPrompt, vButtons, vTitle, vHelpfile, vContext)
    End If
    If hHook <> 0 Then UnhookWindowsHookEx hHook
End Sub

• The code defines a public subroutine called MsgBoxCustom which takes several parameters.
• The ByRef vID, As the Variant parameter, is used to pass the value of the selected button back to the caller.
• The sPrompt As String parameter specifies the prompt or message to be displayed in the message box.
• The vButtons As Variant parameter specifies the buttons that will be displayed in the message box. It is optional and has a default value of 0.
• The vTitle As Variant parameter is used to specify the title of the message box. It is also an optional parameter with no default value.
• The vHelpfile As Variant parameter is used to specify the help file that will be associated with the message box. It is an optional parameter with no default value.
• The vContext As Variant parameter is used to specify the help context ID for the message box. It is an optional parameter with a default value of 0.
• The function sets up a Windows hook using SetWindowsHookEx with the WH_CBT parameter and the MsgBoxCustom_Proc function as the callback function.
• Depending on the combination of parameters passed to the function, it displays a message box using the MsgBox function with the specified prompt, buttons, title, help file, and help context ID.
• The function then removes the Windows hook using UnhookWindowsHookEx.

Sub Custom_MsgBox_Buttons()
    MsgBoxCustom_Set vbOK, "Play"
    'Replace the Text "OK" with "Play"
    MsgBoxCustom_Set vbCancel, "Pause"
    'Replace the Text "Cancel" with "Pause"
    MsgBoxCustom ans, "Press a button.", vbOKCancel
End Sub

• It Calls the MsgBoxCustom_Set function with vbOK and “Play” as arguments. This replaces the default text for the “OK” button in the custom message box with the text “Play“.
• Calls the MsgBoxCustom_Set function with vbCancel and “Pause” as arguments. This replaces the default text for the “Cancel” button in the custom message box with the text “Pause“.
• Calls the MsgBoxCustom function with ans, “Press a button.”, and vbOKCancel as arguments. This displays a custom message box with “Press a button.” as the message and “Play” and “Pause” as the button labels for the “OK” and “Cancel” buttons, respectively. The user’s button choice is then assigned to the variable ans.

Code Breakdown:

Function DisplayMsg(Prompt As String, _
Optional Title As String = "Message Box", _
Optional Button1Text As String = "OK", _
Optional Button2Text As String = "", _
Optional Button3Text As String = "") As String

• This defines a function called DisplayMsg that takes in a Prompt string as a required argument and three optional arguments: Title, Button1Text, Button2Text, and Button3Text. The function returns a string.

UserForm1.Caption = Title
UserForm1.Label1.Caption = Prompt
UserForm1.CommandButton1.Caption = Button1Text
UserForm1.CommandButton2.Caption = Button2Text
UserForm1.CommandButton3.Caption = Button3Text

• This code sets the captions of the user form’s caption, label, and three command buttons to the values of Title, Prompt, Button1Text, Button2Text, and Button3Text, respectively.

UserForm1.CommandButton1.Visible = (Button1Text <> vbNullString)
UserForm1.CommandButton2.Visible = (Button2Text <> vbNullString)
UserForm1.CommandButton3.Visible = (Button3Text <> vbNullString)

• This part sets the visibility of each command button based on whether its associated text is empty.

UserForm1.CommandButton1.Default = True
UserForm1.Show

• This line sets the first command button as the default (i.e., it will be clicked if the user hits the Enter key) and then shows the user form.

DisplayMsg = UserForm1.Tag

• This code sets the function’s return value to the user form’s Tag property (which is set by the button click event).

Unload UserForm1

• This code unloads (i.e. closes) the user form.

Code Breakdown:

Sub userform_MsgBox()
UserForm1.DisplayMsg "Click OK", Title:="Message Box"

• This code defines a Subroutine named userform_MsgBox which calls the DisplayMsg Function defined in a UserForm (presumably UserForm1) to display custom message boxes with optional custom button text.

If UserForm1.DisplayMsg("Do you want to do it?", _
    Button1Text:="Yes", Button2Text:="No") = "Yes" Then
        MsgBox "Done"
    Else
        MsgBox "Cancelled"
    End If
End Sub

• DisplayMsg displays a message box with the prompt “Click OK” and the default “OK” button. The second call to DisplayMsg displays a message box with the prompt “Do you want to do it?” and custom button text of “Yes” and “No”. If the user selects “Yes”, a message box is displayed with the prompt “Done”. If the user selects “No”, a message box is displayed with the prompt “Cancelled”.
• Press F5 or click the Run button to run the code.
• The Userform will pop up and show the message.

Code Breakdown:

For Row = 4 To 12
    For Col = 2 To 4

• A nested loop is started using the For…Next statements. The outer loop iterates over the rows of the table (from Row 4 to Row 12) and the inner loop iterates over the table’s columns (from Column 2 to Column 4).

Msg = Msg & Cells(Row, Col).Text & vbTab

• The text from each cell is appended to the Msg string, separated by a tab character.

Msg = Msg & vbCrLf

• After each row is processed, a newline character is added to the Msg string.

MsgBox Msg

• Once all the rows and columns have been processed, the Msg string is displayed in a message box.

Code Breakdown:

Private Sub CommandButton1_Click()
    Dim SalesRep As String
    Dim Product As String
    Dim RevEarned As String

• The code first declares three string variables SalesRep, Product, and RevEarned.

   Response = MsgBox("Are you a sales representative?", vbYesNo)

• It displays a message box with a “Yes” and “No” button options, and the response from the user is stored in the Response variable.

If Response  = vbYes Then
        SalesRep = InputBox("Enter your name", "Name")
        Product = InputBox("Enter product name", "Product")
        RevEarned = InputBox("Enter revenue earned", "Revenue Earned")

• If the user selects “Yes,” the code prompts them to enter their name, product name, and revenue earned using input boxes. The values the user enters are then stored in the SalesRep, Product, and RevEarned variables, respectively.

nextRow = WorksheetFunction.CountA(Sheets("Mysheet") _
        .Range("B:B")) + 3
        Sheets("Mysheet").Cells(nextRow, 2).Value = SalesRep
        Sheets("Mysheet").Cells(nextRow, 3).Value = Product
        Sheets("Mysheet").Cells(nextRow, 4).Value = RevEarned
    End If

• Calculates the next available row in column B of “Mysheet” worksheet using the CountA function and stores it in the nextRow variable. The values of SalesRep, Product, and RevEarned are then written to the cells in the next available row of columns B, C, and D of “Mysheet” worksheet, respectively.

If Response  = vbNo Then
        MsgBox "You are not eligible"
    End If
End Sub

• If the user selects “No,” the code displays a message box with “You are not eligible.”