How to Lock User Entries When an Excel Workbook Closes
Question details
The user wants to use a workbook event macro to automatically lock cells containing user-entered values when the Excel workbook is closed.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing workbooks shared through platforms like SharePoint, where specific cells are left editable for initial input but need to be secured against modification after the session ends.
- Observed behavior
- Currently, cells remain editable unless manually protected. The goal state is for an automated script to identify modified cells and lock them immediately upon the user closing the workbook.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings; otherwise, the automated script will not execute.
Use the Workbook_BeforeClose Event Macro
This solution automatically triggers VBA code to lock formula and constant cells right before the workbook is closed by the user.
By utilizing the BeforeClose event inside the ThisWorkbook module, you can automate the protection process. The macro temporarily unprotects the sheet, searches for cells with values, sets their Locked property to True, and reprotects the sheet before the workbook shuts down.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, locate your workbook's project and double-click on 'ThisWorkbook'.
Select 'Workbook' from the left dropdown above the code window, and 'BeforeClose' from the right. Enter your VBA code to unprotect the worksheet, lock the cells (e.g., using SpecialCells for constants), and protect the worksheet again.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your VBA code is retained.

Use the Workbook_BeforeSave Event Macro Alternative
Trigger the cell-locking macro when the user saves the document instead of waiting for them to close it.
Automate Cell Locking Easily in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to use the exact same Workbook_BeforeClose or BeforeSave event codes to lock user entries automatically. It provides a familiar VBA editor and robust sheet protection tools.
- 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top ribbon, and click the 'Developer' tab to access macro functionalities.
- 2. Launch the VBA Editor: Click on the 'Visual Basic' button or press ALT + F11 to open the built-in VBA editor.
- 3. Add the Automation Script: Double-click 'ThisWorkbook' in the Project window and paste your BeforeClose cell-locking macro code.
- 4. Save Your File: Click File > Save As and select the '.xlsm' format to ensure your macros run correctly upon the next closing or saving event.

Frequently Asked Questions
Why isn't my BeforeClose macro running when I close the workbook?
If macros are disabled in your security settings, the BeforeClose event won't trigger. Ensure you have enabled macros in the Trust Center and saved the file in the Macro-Enabled (.xlsm) format.
Can I target a specific range to lock instead of all constant cells?
Yes, you can modify your VBA code to target specific named ranges or explicit cell references (for example, `Range("A1:C10").Locked = True`) instead of applying the lock to all cells containing constants.
Will this macro work properly for workbooks co-authored on SharePoint?
Live co-authoring in Excel Online or SharePoint often disables macros or handles saving events differently. For the BeforeClose event to execute reliably, users generally need to open the file in the desktop application.




