How to Create an Excel VBA Macro to Clear Form Cells Automatically
Question details
The user needs a VBA macro in Excel to clear specific fillable cells on a protected sheet via a clickable button, and also automatically clear them when the workbook is closed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a reusable form where user-entered data in specific cells must be reset for the next use, both manually via a button and automatically upon exit.
- Observed behavior
- The goal is to reset fifteen specific cells (e.g., F6, H6) on a locked sheet named 'Signing Sheet' without breaking the sheet protection.
Ensure the Developer tab is enabled in your spreadsheet program and verify the password for your protected sheet, as the macro will need to temporarily unprotect and reprotect it to clear the cells.
Create a VBA Macro to Clear Specific Cells and Assign to a Button
Write a VBA sub procedure that unprotects the sheet, clears the specific form cells, reprotects the sheet, and assign this macro to a clickable form button.
To clear specific cells on a protected sheet, the VBA script must first lift the sheet protection, use the ClearContents method on the targeted range, and then reapply the protection. This ensures that users can clear the form without permanently unlocking the document.
Press the Alt + F11 shortcut keys to open the Visual Basic for Applications (VBA) Editor.
In the top menu, click 'Insert' > 'Module'. A new blank module window will appear.
Type the VBA code: Sub ClearForm() Sheets("Signing Sheet").Unprotect "YourPassword" Range("F6,H6,F7,H7,I7,L7,F8,H8,I8,L8,H11,E24,J24,E25,J25").ClearContents Sheets("Signing Sheet").Protect "YourPassword" End Sub
Close the VBA Editor. Go to the 'Developer' tab on the ribbon, click 'Insert', and select the 'Button (Form Control)'. Draw the button on your sheet, select 'ClearForm' in the Assign Macro dialog box, and click 'OK'.
Trigger the Clear Macro Automatically on Workbook Close
Use workbook events to execute the previously created clear macro automatically every time the user closes the form or workbook.
Easily Run VBA Macros and Clear Excel Forms with WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA macros, allowing you to seamlessly automate tasks like clearing form cells. With full compatibility, you can run your existing Excel macros and streamline your workflows efficiently.
- 1. Open Your Macro File: Launch WPS Spreadsheet and open your .xlsm file.
- 2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'Macros' or 'Visual Basic' to view and edit your scripts.
- 3. Insert Form Controls: Use the 'Insert' option under the Developer tab to add buttons, check boxes, or combo boxes directly onto your sheet to trigger your VBA code effortlessly.
Frequently Asked Questions
Why does my VBA macro return an error on a protected sheet?
By default, VBA cannot modify locked cells on a protected worksheet. You must temporarily lift the protection by adding 'ActiveSheet.Unprotect "password"' at the beginning of your macro and 'ActiveSheet.Protect "password"' at the end to lock it again.
How do I assign a macro to a button?
Go to the Developer tab, click 'Insert', and choose 'Button (Form Control)'. Draw the button on your worksheet. A prompt will automatically appear asking you to select the macro you wish to assign to the button.
Can I run my Excel VBA macros in WPS Spreadsheet?
Yes, WPS Spreadsheet has extensive support for VBA macros. You can run, edit, and create VBA scripts just as you would in Microsoft Excel by accessing the Developer tab.
