How to Use vbYes Correctly in an Excel VBA Cancel Button
Question details
The user needs to make an Excel VBA UserForm Cancel button accurately distinguish between Yes and No responses in a MsgBox confirmation dialog.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a confirmation dialog for a Cancel button in an Excel VBA UserForm, but the vbYes and vbNo responses are executing the same action.
- Observed behavior
- Both 'Yes' and 'No' responses from the MsgBox behave identically because the return value is incorrectly stored as a Boolean variable.
Verify that your UserForm design is finalized and ensure your VBA Editor is open to the code module containing the Cancel button's Click event.
Declare the MsgBox Result Variable as a Long Data Type
Correctly storing the MsgBox return value as a Long prevents vbYes and vbNo from automatically resolving to the same Boolean True condition.
The MsgBox function in Excel VBA returns an integer value representing the specific button the user clicked. For example, vbYes returns 6 and vbNo returns 7.
When you declare the result variable as a Boolean, VBA implicitly converts any non-zero numeric value to True. Because both 6 and 7 are non-zero, both Yes and No clicks evaluate to True, causing the button's logic to fail. Changing the data type to Long resolves this.
Press Alt + F11 in Excel to launch the VBA Editor, then double-click your UserForm in the Project Explorer.
Right-click the Cancel button on your UserForm and select 'View Code' to locate the Private Sub cmdCancel_Click() event.
Find the line reading 'Dim ExitFrm As Boolean' and change it to 'Dim ExitFrm As Long' or 'Dim ExitFrm As VbMsgBoxResult'.
Update the macro code to compare the Long variable against the vbYes constant: Private Sub cmdCancel_Click() Dim ExitFrm As Long ExitFrm = VBA.MsgBox("Are you sure you want to exit without saving?", vbYesNo + vbQuestion + vbDefaultButton1) If ExitFrm = vbYes Then Unload Me End Sub
Write and Execute VBA Macros in WPS Spreadsheet
WPS Office provides robust built-in support for VBA and macros, allowing you to seamlessly create UserForms, write scripts, and execute MsgBox commands exactly as you would in Microsoft Excel.
- 1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the Options menu, and ensure the Developer tab is enabled in your customized ribbon.
- 2. Launch the VBA Editor: Click on the Developer tab and select 'Visual Basic' (or simply press Alt + F11) to open the VBA editing environment.
- 3. Create a UserForm: Right-click on your active project in the Project Explorer window, select Insert, and choose 'UserForm' to build your custom interface.
- 4. Apply the MsgBox Code: Double-click your Cancel button inside the WPS VBA editor and paste the corrected Long variable MsgBox code to handle vbYes logic properly.
Frequently Asked Questions
Why does my VBA MsgBox always return True regardless of the button clicked?
If you store the MsgBox result in a Boolean variable, VBA automatically converts any non-zero integer return value (like 6 for Yes and 7 for No) into True. You must declare the variable as a Long or VbMsgBoxResult to accurately read the exact button clicked.
What data type does the MsgBox function return in Excel VBA?
The MsgBox function returns an integer categorized under the VbMsgBoxResult enumeration. Therefore, you should store its output in a variable declared as VbMsgBoxResult or Long.
How do I add a default button to my VBA MsgBox?
You can set a default button by adding the constant vbDefaultButton1, vbDefaultButton2, or vbDefaultButton3 to your MsgBox prompt arguments. For example: ExitFrm = VBA.MsgBox("Exit?", vbYesNo + vbQuestion + vbDefaultButton2).
