VBA MsgBox in Excel can display a few standard default buttons which are labeled as OK, Cancel, Yes, No, Abort, Retry. Customizing these buttons is not an easy task. However, it can be done. In this article, we will demonstrate two examples of how to create VBA MsgBox buttons in Excel.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
Introduction to MsgBox in Excel VBA
A message box is a dialogue box that is used to display information to the users. A message box normally contains a title, a prompt, buttons, and a close icon.
Components | Function |
---|---|
Title | Displays the title of the message box |
Prompt | Displays tables and data |
Buttons | Takes response from users |
Close Icon | Closes the message box |
Syntax of VBA MsgBox
MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )
Arguments | Required/Optional | Function |
---|---|---|
prompt | Required | Displays message |
buttons | Optional | Determines what buttons and Icons will be shown in the message box |
title | Optional | specifies the title of the message box |
helpfile | Optional | specifies the help file to be used if the user clicks the Help button |
context | Optional | specifies the context ID for the Help topic |
Commonly Used Buttons in VBA MsgBox
Buttons | Integer Value | Function |
---|---|---|
OK | vbOKOnly | Displays only the OK button. |
OK/Cancel | vbOKCancel | Displays both the OK and Cancel buttons. |
Yes/No | vbYesNo | Displays both the Yes and No buttons. |
Yes/No/Cancel | vbYesNoCancel | Displays all three buttons (Yes, No, and Cancel). |
Abort/Retry/Ignore | vbAbortRetryIgnore | Displays Abort, Retry and Ignore buttons |
Retry/Cancel | vbRetryCancel | Displays Retry and Cancel button |
HelpButton | vbMsgBoxHelpButton | Displays Help button |
How to Launch VBA Editor in Excel
To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Then go to the Insert tab and click on Module to open the code Module.
How to Create a User Form
Once the Microsoft Visual Basic window is open, click on the Insert tab and select Userform.
As a result, Userform will open with a Toolbox.
2 Examples to Create Custom Buttons for VBA MsgBox in Excel
In this article, we will walk you through two examples to create custom buttons for VBA MsgBox in Excel.
1. Use Callback Function to Create Custom Buttons in MsgBox in Excel
In this way, you can customize the default buttons of the MsgBox. However, you won’t be able to change the size or position of these buttons.
In this example, we will create two custom buttons using the Callback function. We will name one button “Play” and the other button “Pause”. The procedure to do this is discussed below.
Steps:
- First of all, launch the Microsoft Visual Basic code editor and insert a Module.
- In the module, write down the following code.
#If VBA7 Then
Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" _
() As Long
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
Private Declare PtrSafe Function SetDlgItemText Lib "user32" _
Alias "SetDlgItemTextA" _
(ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long, ByVal _
lpString As String) As Long
Private Declare PtrSafe Function CallNextHookEx Lib "user32" _
(ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As _
LongPtr, lParam As Any) _
As LongPtr
Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As LongPtr) As Long
Private hHook As LongPtr
#Else
Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" _
() As Long
Private Declare PtrSafe 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 PtrSafe Function SetDlgItemText Lib "user32" Alias _
"SetDlgItemTextA" _
(ByVal hDlg As Long, ByVal nIDDlgItem As Long, ByVal lpString As _
String) As Long
Private Declare PtrSafe Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, _
lParam As Any) _
As Long
Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
Private hHook As Long
#End If
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
Private sMsgBoxDefaultLabel(1 To 7) As String
Private sMsgBoxCustomLabel(1 To 7) As String
Private bMsgBoxCustomInit As Boolean
Private Sub MsgBoxCustom_Init()
Dim ID As Integer
Dim vA As Variant
vA = VBA.Array(vbNullString, "OK", "Cancel", "Abort", "Retry", _
"Ignore", "Yes", "No")
For ID = 1 To 7
sMsgBoxDefaultLabel(ID) = vA(ID)
sMsgBoxCustomLabel(ID) = sMsgBoxDefaultLabel(ID)
Next ID
bMsgBoxCustomInit = True
End Sub
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
Public Sub MsgBoxCustom_Reset(ByVal ID As Integer)
Call MsgBoxCustom_Set(ID)
End Sub
#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
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
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
Note: Adapted from wellsr.com
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 is used to specify 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 is used to specify 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 the value “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. If true, 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 label for the custom button at the specified index is set to the default label.
- 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.
- When called, 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 version of VBA being used.
- If VBA7 or higher is being used, then 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 both 32-bit and 64-bit systems), and returns a value of type Long.
- The function is used as a hook procedure that gets called when a message is sent to a message box dialog box. Specifically, the function is designed to intercept the HCBT_ACTIVATE message, which is 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).
- Finally, the function returns the result of calling the CallNextHookEx function, which passes the message on 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 Variant parameter is used to pass the value of the selected button back to the caller.
- The sPrompt As String parameter is used to specify the prompt or message that will be displayed in the message box.
- The vButtons As Variant parameter is used to specify the buttons that will be displayed in the message box. It is an optional parameter with 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.
- Finally, run the code and you will get a message box with a “Play” and a “Pause” button.
2. Create Userform to Add Custom Buttons in MsgBox
This method helps you customize the numbers, size, labels, postion etc of a MsgBox.
In this example, we will create a Userform to act as a MsgBox. We can add different custom buttons in Userform. The steps to do so are described in the following section.
Steps:
- First of all, insert a Userform from the Insert tab.
- The Userform will appear with a Toolbox. Drag a Label from the ToolBox to the Userform. This Label will be used to show the text of MsgBox.
- After that, we will add some buttons. Drag a CommandButton from the Toolbox and drop it in the Userform.
- Similarly, add two more buttons. You can add as many buttons as you need and customize them as per your need.
- You can position and resize the elements as per your need.
- After that, double-click on CommandButton1.
- Then write the following code. This code will run when the CommandButton1 is clicked.
Private Sub CommandButton1_Click()
Me.Tag = CommandButton1.Caption
Me.Hide
End Sub
- Similarly, double-click CommandButton2 and Then write the following code.
Private Sub CommandButton2_Click()
Me.Tag = CommandButton2.Caption
Me.Hide
End Sub
Private Sub CommandButton3_Click()
Me.Tag = CommandButton3.Caption
Me.Hide
End Sub
- After that, we will need to write a function to show Userform as MsgBox.
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
UserForm1.Caption = Title
UserForm1.Label1.Caption = Prompt
UserForm1.CommandButton1.Caption = Button1Text
UserForm1.CommandButton2.Caption = Button2Text
UserForm1.CommandButton3.Caption = Button3Text
UserForm1.CommandButton1.Visible = (Button1Text <> vbNullString)
UserForm1.CommandButton2.Visible = (Button2Text <> vbNullString)
UserForm1.CommandButton3.Visible = (Button3Text <> vbNullString)
UserForm1.CommandButton1.Default = True
UserForm1.Show
DisplayMsg = UserForm1.Tag
Unload UserForm1
End Function
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 an empty string or not.
UserForm1.CommandButton1.Default = True
UserForm1.Show
- This line makes the first command button as the default (i.e. it will be clicked if the user hits the Enter key), then shows the user form.
DisplayMsg = UserForm1.Tag
- This code sets the return value of the function to the Tag property of the user form (which is set by the button click event).
Unload UserForm1
- This code unloads (i.e. closes) the user form.
- Next, insert a Module to create a sub procedure.
- Then write the following code in the module.
Sub userform_MsgBox()
UserForm1.DisplayMsg "Click OK", Title:="Message Box"
If UserForm1.DisplayMsg("Do you want to do it?", _
Button1Text:="Yes", Button2Text:="No") = "Yes" Then
MsgBox "Done"
Else
MsgBox "Cancelled"
End If
End Sub
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
- The first call to 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”.
- Finally, press F5 or click the Run button to run the code.
- As a result, the Userform will pop up and show the message.
Custom Procedures: MsgBox to Deal with Different Circumstances
Now we will show you some custom procedures where we will deal with different circumstances.
Example 1: Insert a Table into a Message Box
We want to insert the following table into a MsgBox. We can do this easily by following the steps provided below.
Steps:
- First, insert a Module in the Microsoft Visual Basic window.
- Then insert the following code in the Module.
Sub Table_MsgBox()
Dim Msg As String, Row As Integer, Col As Integer
Msg = ""
For Row = 4 To 12
For Col = 2 To 4
Msg = Msg & Cells(Row, Col).Text & vbTab
Next Col
Msg = Msg & vbCrLf
Next Row
MsgBox Msg
End Sub
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 columns of the table (from Column 2 to Column 4).
Msg = Msg & Cells(Row, Col).Text & vbTab
- Within the loop, 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.
- Finally, run the code to get your desired result.
Example 2: Show Message Box on Opening a File
Showing a message box on opening a file is a very simple task. Follow the steps described below to do it.
Steps:
- First, open a Module and write the code given below.
Sub auto_open()
MsgBox "Hello"
End Sub
- Then close the workbook and reopen it.
- Once the workbook is open, a message box will appear with the message “Hello”.
Example 3: Insert Data into Excel Table Using MsgBox
In this section, we will insert data into an Excel table using the message box. We will use the following procedure for this purpose.
Steps:
- First, open an Excel sheet and create an empty table.
- Next, we will add a Command Button to insert data in the table.
- To add the Command Button, click on the Developer tab and go to,
Developer → Insert → Command Button (ActiveX Control)
- Then drag your cursor to create the Command Button.
- After that, double-click on the button to open a Module.
- Then write the following code in it.
Private Sub CommandButton1_Click()
Dim SalesRep As String
Dim Product As String
Dim RevEarned As String
Response = MsgBox("Are you a sales representative?", vbYesNo)
If Response = vbYes Then
SalesRep = InputBox("Enter your name", "Name")
Product = InputBox("Enter product name", "Product")
RevEarned = InputBox("Enter revenue earned", "Revenue Earned")
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
If Response = vbNo Then
MsgBox "You are not eligible"
End If
End Sub
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)
- Next, 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 then prompts the user to enter their name, product name, and revenue earned using input boxes. The values entered by the user 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
- The code then 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 the text “You are not eligible.”
- Now click on CommandButton1 and insert the data as shown in the following video.
How to Get and Manipulate MsgBox Result in Excel VBA
We can manipulate the MsgBox result so that when a button is clicked in the message box, it does a task assigned to it. In this example, we want to show a message box that says “You clicked OK” when the OK button is clicked in the message box. Similarly, when you click Cancel, it needs to say, “You clicked cancel.”.
Steps:
- First of all, type the following VBA code in the module.
Sub Manipulate_Result()
If MsgBox("Click any button", vbOKCancel, _
"Manipulate Result") = vbOK Then
MsgBox "You clicked OK"
Else
MsgBox "You clicked cancel"
End If
End Sub
- Now run the code to get your desired output.
Things to Remember
- Do not use more than ten characters to define custom labels using MsgBoxCustom_Set.
- To get back the default button labels, you can use MsgBoxCustom_Reset.
Frequently Asked Questions
1. What are the different buttons in MsgBox VBA?
There are several buttons in MsgBox such as OK, Yes/No/Cancel, Abort/Retry/Ignore, etc. You can explore many more while using the MsgBox.
2. What is the default button in MsgBox VBA?
VBA Message Box has OK as the default button. Although you can change it to display other buttons such as OK/Cancel, Yes/No, Yes/No/Cancel, Abort/Retry/Ignore, etc.
3. What is the difference between message box and MsgBox?
There are two ways to use message boxes in VB.NET: the MessageBox class, which is native to.NET and has a somewhat different syntax, and the MsgBox function, which is a part of the Microsoft Visual Basic namespace and is coded in a manner quite similar to pre-.NET versions of VB.
4. How do I write multiple lines in MsgBox VBA?
You can use the VBA constant for carriage return and line feed, VbCrLf to write multiple lines in MsgBox.
Conclusion
In this article, we have provided you with two ways to customize VBA MsgBox buttons. You can use the Callback function or create a UserForm to act as a MsgBox. USerForm gives you more freedom to customize button numbers, labels, and positions, whereas the first method helps you customize the default MsgBox button labels. You can use either of the two methods as per your needs.