VBA & Macro Problems

How to Use Excel VBA Code to Lock Cells After Data Entry

Phi Hung Vo Sep 30, 2026 870 views

Question details

The user needs a VBA script to automatically lock and protect cells after data is entered and the workbook is saved, preventing other users from editing previously inputted information.

How to Lock Cells After Data Entry Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Managing a shared workbook where multiple users enter data over time, and past entries must be secured against accidental or intentional modifications.
Observed behavior: Without automation, cells remain editable by any user after data entry unless the sheet is manually protected each time.

Before you start

Before applying VBA macros, create a secure backup of your workbook, as VBA actions cannot be undone via the standard Undo feature. Additionally, ensure your file will be saved as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Automate Cell Protection Using the BeforeSave VBA Event

This solution uses Excel's built-in VBA editor to trigger a protection macro right before the workbook is saved, securing all locked cells from further edits.

By placing your macro in the Workbook_BeforeSave event, Excel will automatically enforce sheet protection every time a user saves the file. This ensures that any newly entered data is immediately locked for subsequent users.

Open the VBA Editor

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

Insert a New Module

In the top menu, click on 'Insert' and select 'Module'. This will create a new blank window for your code.

Enter the Protection Code

Copy and paste the following code into the module window: Sub ProtectSheet() ActiveSheet.Protect Password:="YourPassword", UserInterfaceOnly:=True End Sub

Link to the BeforeSave Event

In the Project Explorer pane on the left, double-click 'ThisWorkbook'. In the code window that appears, select 'Workbook' from the left dropdown and 'BeforeSave' from the right dropdown. Call your macro by typing 'Call ProtectSheet' inside this event.

Save as Macro-Enabled

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

Automate Cell Protection Using the BeforeSave VBA Event

Security Limitation: Worksheet protection is not a strong security boundary. It is designed to prevent accidental edits rather than malicious tampering, as users with sufficient technical knowledge may bypass it.

Protect and Manage Spreadsheets Easily with WPS Office

WPS Spreadsheets provides seamless compatibility with Excel macros and offers highly intuitive built-in tools for locking cells and protecting sheets, ensuring your shared data remains secure.

1. Open your Workbook in WPS Spreadsheets: Launch WPS Office and open your .xlsx or .xlsm data entry file.
2. Select Cells to Lock or Unlock: Highlight the specific cells you want to leave editable, right-click, select 'Format Cells', go to the 'Protection' tab, and uncheck 'Locked'.
3. Apply Sheet Protection: Navigate to the 'Review' tab on the top ribbon and click on 'Protect Sheet'.
4. Set a Password: Enter a secure password and choose the permissions you want to grant to other users, then click 'OK' to enforce the protection.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats and macrosIntuitive Review tab allows for quick cell locking without the need for complex VBA codingLightweight and fast processing, ideal for managing large shared data entry filesAdvanced user permissions feature for precise control over who can edit specific ranges

microsoft office alternative - wps office

Frequently Asked Questions

Can I unlock the cells later if I need to make corrections?

Yes. You can manually unprotect the sheet by going to the Review tab and selecting 'Unprotect Sheet', then entering the password. Alternatively, you can write an 'UnprotectSheet' VBA macro that prompts for the password before allowing edits.

Why isn't my VBA code running when other users open the file?

Macro execution depends on the user's Trust Center settings. If macros are disabled by default on their device, the VBA code will not run. Users must click 'Enable Content' when opening the workbook for the BeforeSave event to trigger.

What does 'UserInterfaceOnly:=True' do in the VBA code?

The 'UserInterfaceOnly:=True' argument applies protection to the user interface (preventing manual user edits) but allows other VBA macros to continue modifying the locked cells without needing to unprotect the sheet first.

Is it possible to lock only specific cells instead of the whole sheet?

Yes. By default, all cells in Excel are set to 'Locked'. To lock only specific cells, you must first highlight all cells, format them to be 'Unlocked', and then re-apply the 'Locked' status only to the cells you want to protect before running the protection macro.

Excel & Data Problems