VBA & Macro Problems

How to Lock User Entries When an Excel Workbook Closes

Khadija KhanKhadija Khan Nov 11, 2026 869 views

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.

How to Lock User Entries When an Excel Workbook Closes
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Access ThisWorkbook

In the Project Explorer pane on the left, locate your workbook's project and double-click on 'ThisWorkbook'.

3
Insert the BeforeClose Code

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.

4
Save as Macro-Enabled

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your VBA code is retained.

Use the Workbook_BeforeClose Event Macro
Modify Code Parameters: Remember to replace the target sheet name and protection password in the VBA script with your actual worksheet name and desired password.
WPS Office Macros

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. 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top ribbon, and click the 'Developer' tab to access macro functionalities.
  2. 2. Launch the VBA Editor: Click on the 'Visual Basic' button or press ALT + F11 to open the built-in VBA editor.
  3. 3. Add the Automation Script: Double-click 'ThisWorkbook' in the Project window and paste your BeforeClose cell-locking macro code.
  4. 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.
Fully compatible with Microsoft Excel Macro-Enabled formats (.xlsm).Built-in VBA editor for writing, debugging, and executing event macros.Advanced sheet protection features to secure sensitive shared data.Free, lightweight, and user-friendly interface.
microsoft office alternative - wps office

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.