VBA & Macro Problems

Fix a VBA Cancel Button That Clears a Form

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 869 views

Question details

Prevent a VBA form from being cleared when the user clicks Cancel in an OK/Cancel message box.

Fix a VBA Cancel Button That Clears a Form
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.
Before you start

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.

Solution 1Recommended

Clear the form only when the user clicks OK

Use the MsgBox return value to run the existing clearing code only after confirmation.

1
Open the VBA procedure

Open the Visual Basic Editor from the Developer tab and find the button event or procedure that displays the clear-data message.

2
Check the selected button

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

3
Move the clearing code

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.

4
Test both choices

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.

Clear the form only when the user clicks OK
Why this works: MsgBox returns a value identifying the selected button. The clearing code inside the If block runs only when that value is vbOK.
Free Microsoft Office alternative

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.

A familiar interface for everyday document and spreadsheet workCompatibility with Microsoft Office formats, including DOCX, XLSX, and PPTXA free, lightweight option for viewing and editing Office filesReview form-related spreadsheets while keeping macro compatibility in mind
microsoft office alternative - wps office

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.