VBA & Macro Problems

Automatically Lock Excel Rows When Sum Equals Zero via VBA

Khadija Khan Oct 1, 2026 868 views

Question details

The user needs an automated way to lock specific rows in an Excel worksheet if the sum of the row equals zero, while keeping all other rows and columns editable.

How to Automatically Lock Excel Rows When Their Sum Equals Zero

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing large datasets (e.g., 1,000+ rows) where rows totaling zero must be protected from further manual edits.
Observed behavior: Manually checking the sum and changing cell protection settings for thousands of rows is impractical and time-consuming, requiring an automated VBA solution.

Before you start

Ensure you have a backup of your workbook before running VBA scripts. Remember that macros are only supported on the desktop version of Excel, and you must save the file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Automate Row Locking with a VBA Macro

Use a VBA script to dynamically evaluate each row's total and apply cell protection automatically if the sum equals zero.

Using VBA allows you to check the sum of specific ranges across thousands of rows instantly. If the condition is met, the script locks the corresponding cells. You can configure this macro to run automatically when saving the workbook or trigger it manually.

Because all cells in an Excel worksheet are locked by default (but only take effect when the sheet is protected), your script should first unprotect the sheet, unlock all data cells, lock only the target rows, and finally re-protect the sheet.

Open the VBA Editor

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

Insert a New Module or Workbook Event

To run the script manually, click Insert > Module. To run it automatically upon saving, double-click 'ThisWorkbook' in the Project Explorer and select the 'Workbook_BeforeSave' event.

Write the VBA Code

Write a script that loops through your target rows. Use a line like 'If Application.WorksheetFunction.Sum(Range("A" & i & ":Z" & i)) = 0 Then' followed by 'Rows(i).Locked = True'.

Apply Worksheet Protection

At the end of your script, enforce the locking by adding 'ActiveSheet.Protect Password:="YourPassword"'. Cell locking will not function without protecting the worksheet.

Save as Macro-Enabled

Close the VBA editor and go to File > Save As. Choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown menu.

Enable Macros for Users: Anyone opening this workbook must click 'Enable Content' or have macros enabled in their Trust Center settings for the automated locking to function.

Automate Row Locking and Macros with WPS Spreadsheet

WPS Spreadsheet provides full support for Excel macros and VBA scripts, allowing you to easily automate tasks like locking rows based on cell sums.

1. Open your file in WPS: Launch WPS Spreadsheet and open your .xlsm file containing the data.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor'.
3. Paste and Run the Script: Paste your row-locking VBA script into the module and click Run, exactly as you would in Microsoft Excel.
4. Save and Protect: Save your document. The worksheet protection and dynamically locked cells will function perfectly.

Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Built-in support for executing and editing VBA macros.Advanced cell protection and worksheet security features.Lightweight software with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my locked rows still editable after running the VBA script?

The 'Locked' property of a cell only takes effect when worksheet protection is active. Ensure your VBA script includes the ActiveSheet.Protect command at the end, or manually protect the sheet via the Review tab.

Can I make this macro run automatically every time the workbook is saved?

Yes. Instead of placing the code in a standard module, open the 'ThisWorkbook' module in the VBA Editor and place your code inside the 'Private Sub Workbook_BeforeSave' event.

Does this VBA method work in Excel for Web or Mobile?

No. VBA macros are only supported in the desktop versions of Microsoft Excel on Windows and Mac. Users editing the file on web or mobile platforms will not be able to execute the script to lock rows.

How do I unlock the rows later if the data needs to change?

You will need to unprotect the worksheet first. This can be done manually by going to Review > Unprotect Sheet (entering the password if prompted), or by running another VBA script using ActiveSheet.Unprotect.

Excel & Data Problems