Fix a VBA Cancel Button That Clears a Form
Question details
Prevent a VBA form from being cleared when the user clicks Cancel in an OK/Cancel message box.

- Product
- Microsoft Office VBA
- Device & OS
- not provided
- Scenario
- A VBA procedure asks for confirmation before clearing controls on a form.
- Observed behavior
- Clicking Cancel still clears the form because the procedure does not check the MsgBox return value.
Open the Office file containing the form and locate the VBA procedure that displays the confirmation message. Identify the loop or statements that clear the form controls.
Clear the form only when the user clicks OK
Use the MsgBox return value to run the existing clearing code only after confirmation.
Open the Visual Basic Editor from the Developer tab and find the button event or procedure that displays the clear-data message.
Replace the standalone message box call with an If statement: If MsgBox("You are about to clear the data for the current record", vbOKCancel, "Clear Data") = vbOK Then
Place the existing loop or statements that clear the controls immediately below the If statement, then add End If after the clearing code. Make sure no clearing statements run before the check.
Run the form and click the button that clears it. Choose Cancel to confirm the data remains, then choose OK to confirm the controls are cleared.

Exit the VBA procedure when the user clicks Cancel
Use an early exit when the clearing code should remain after the confirmation check.
Work with Office files in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents, spreadsheets, and presentations. Open compatible files to make the transition easier; VBA features and macros may not work the same way across products or editions.

Frequently Asked Questions
Why does Cancel still clear my VBA form?
The vbOKCancel argument only displays the buttons; it does not stop the procedure. Compare the MsgBox return value with vbOK or vbCancel before running the clearing code.
How do I run the clearing loop only after OK is clicked?
Put the MsgBox call in an If statement that compares its result with vbOK. Place the clearing loop between that If statement and End If.
Can I stop the whole procedure when Cancel is clicked?
Yes. Before the clearing code, check whether MsgBox returns vbCancel and use Exit Sub. In a Function procedure, use Exit Function instead.
Where do I edit the message box code?
Open the Visual Basic Editor from the Office application's Developer tab, then locate the form button event or other procedure containing the MsgBox call.




