How to Lock or Unlock Excel Rows Based on a Yes/No Condition Using VBA
Question details
The user needs to dynamically lock specific cells (e.g., B2:D2) when a target cell (e.g., A2) contains 'Yes', and unlock them when it contains 'No', but their current VBA macro is failing to execute properly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a controlled data entry form or restricted worksheet where the editing permissions of certain cells are dictated by the value of a specific condition cell.
- Observed behavior
- The applied VBA macro does not work as expected, failing to update the locked property of the target cells or resulting in an error due to worksheet protection conflicts.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Excel or WPS Spreadsheet security settings before running the code.
Use a Standard VBA Macro to Update Cell Lock Status
Run a specific macro that temporarily unprotects the sheet, evaluates each row for the 'Yes' or 'No' condition, updates the cell lock properties, and then reprotects the sheet.
When applying a VBA macro to change cell locks, it is crucial to place the code in the correct module and ensure the sheet is unprotected before the code modifies cell properties. If a sheet is protected, VBA cannot change the 'Locked' status of cells unless explicitly instructed to unprotect it first.
Open your workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.
In the top menu, click Insert > Module to create a new standard module where the code will be placed.
Copy and paste the following code into the module window: Sub UpdateLockCondition() Dim R As Range ActiveSheet.Unprotect For Each R In Range("A2", Range("A" & Rows.Count).End(xlUp)) Intersect(Range("B:D"), R.EntireRow).Locked = R = "Yes" Next ActiveSheet.Protect End Sub
Close the VBA editor and return to your worksheet. Press ALT + F8, select UpdateLockCondition from the Macro dialog box, and click Run.
Automate Locking with a Worksheet Change Event
Use an event macro that automatically locks or unlocks cells the exact moment a user types 'Yes' or 'No' into the condition cell.
Manage Macros and Cell Protection with WPS Spreadsheet
WPS Office fully supports VBA macros, allowing you to run conditional locking scripts just like in Microsoft Excel. You can seamlessly manage worksheet protection and automate your workflows in a free, lightweight environment.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing your conditional data.
- 2. Enable Macros: If prompted by a security warning at the top of the screen, click 'Enable Macros' to allow the VBA code to execute.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, enable it in the settings, then click 'Visual Basic' or press ALT + F11.
- 4. Run Your Macro: Click 'Macros' in the Developer tab, select your locking macro from the list, and click 'Run' to apply the cell protection.
Frequently Asked Questions
Why do I get a run-time error when the macro tries to lock the cells?
This usually happens because the worksheet is already protected. Your VBA code must include the line ActiveSheet.Unprotect before attempting to modify any cell properties, and ActiveSheet.Protect at the end to re-secure the worksheet.
How can I apply this macro to a different column range instead of B:D?
In the VBA code line that reads Intersect(Range("B:D"), R.EntireRow), simply change "B:D" to your desired column range, such as "E:G" or "C:F".
Does WPS Spreadsheet support this exact VBA code?
Yes, WPS Spreadsheet supports VBA. You can use the exact same code to conditionally lock and unlock cells, provided the VBA module is installed and enabled in your version of WPS Office.
