VBA & Macro Problems

Protect Excel Worksheets While Allowing VBA to Edit Locked Cells

WPS Content Manager Oct 9, 2026 869 views

Question details

The user wants to protect an Excel worksheet to prevent manual data entry in specific cells, while still allowing background VBA macros to edit and update those locked cells without triggering an error.

Protect Excel Worksheets While Allowing VBA to Edit Locked Cells

Product: Excel
Device & OS: not provided
Scenario: Setting up a secure automated workbook where users have restricted manual edit access, but macros require full write access to process and update locked data.
Observed behavior: Standard worksheet protection blocks both manual user edits and VBA macro edits, causing runtime errors when a macro attempts to write data to a protected cell.

Before you start

Ensure you have unlocked any specific cell ranges that users are allowed to edit via the Format Cells menu, and create a backup of your workbook before applying macro-based passwords.

Solution 1Recommended

Apply UserInterfaceOnly Protection via Workbook_Open Event

Use the UserInterfaceOnly argument in VBA to allow macros to bypass worksheet protection, ensuring the code runs smoothly while users remain restricted.

By default, protecting a sheet locks out both users and macros. Setting UserInterfaceOnly to True changes this behavior.

Because the UserInterfaceOnly setting is not saved when the workbook is closed, you must reapply this protection every time the workbook is opened. Placing the code in the Workbook_Open event ensures it activates automatically.

Unlock user-editable cells

Select the cells you want users to be able to edit. Right-click, choose 'Format Cells', go to the 'Protection' tab, uncheck the 'Locked' box, and click OK.

Open the Visual Basic Editor

Press ALT + F11 on your keyboard to open the VBA Editor.

Access the ThisWorkbook module

In the Project Explorer pane on the left, double-click on 'ThisWorkbook' to open its code window.

Insert the Workbook_Open macro

Paste the following code into the window: Private Sub Workbook_Open() For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="YourPassword", UserInterfaceOnly:=True Next ws End Sub

Save as a Macro-Enabled Workbook

Save your file as an Excel Macro-Enabled Workbook (*.xlsm) so the code executes the next time you open the file.

Apply UserInterfaceOnly Protection via Workbook_Open Event

Session-Specific Setting: The UserInterfaceOnly property is wiped from memory as soon as the file is closed. Do not attempt to run this once in a standard module and expect it to persist; it must run upon every startup.

Solution 2

Protect the VBA Project Code

Secure the backend VBA code to prevent users from viewing the macro logic or discovering the worksheet protection password stored in plain text.

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides exceptional compatibility with Microsoft Excel macros and VBA scripts. You can easily protect worksheets, apply UserInterfaceOnly code, and run advanced automated tasks securely using WPS Spreadsheet.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm workbook.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the code editor.
3. Add the protection code: Double-click 'ThisWorkbook' in the Project Explorer and paste the UserInterfaceOnly macro.
4. Save and deploy: Save the changes and restart the workbook to verify that macros can edit protected cells while users are restricted.

Fully compatible with Microsoft Excel VBA code and macro formats (.xlsm, .xlsb)Intuitive interface for managing worksheet and workbook protectionLightweight application that opens large macro-enabled files quickly and reliablyCost-effective alternative for powerful data analysis and automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail on a protected sheet even though I ran the UserInterfaceOnly code previously?

The UserInterfaceOnly setting is not saved within the file properties. If you applied it manually or via a standard module but did not place it inside the Workbook_Open event, the sheet reverts to standard strict protection (blocking VBA) the next time you open the workbook.

Can I unlock certain cells for users while protecting the rest of the sheet via VBA?

Yes. Before the sheet is protected, you must unlock the specific ranges users are allowed to edit. Select the target cells, right-click and choose Format Cells, go to the Protection tab, and uncheck the Locked option. Once the sheet is protected, users can only edit those unlocked cells.

Is the VBA password protection completely secure?

Locking your VBA project prevents normal viewing and casual tampering by standard users, effectively hiding your worksheet protection passwords. However, it is not a cryptographically strong security boundary and can be bypassed by determined attackers with specialized password-removal tools.

Excel & Data Problems