How to Lock or Unlock Excel Cells Based on Another Cell Using VBA
Question details
The user needs to automatically lock or unlock specific cells in an Excel worksheet dynamically, depending on the text entered into another designated cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a dynamic form or spreadsheet where user input in one column (e.g., entering 'Open') controls the editability of cells in an adjacent or corresponding column.
- Observed behavior
- Cells need to automatically toggle their locked property via a Worksheet_Change macro, temporarily bypassing sheet protection to apply the changes.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the cells you want users to trigger the change from are manually unlocked before applying worksheet protection.
Use VBA Worksheet_Change Event to Conditionally Lock Cells
This method uses a background macro to detect when a specific cell's value changes, temporarily unprotects the sheet, locks or unlocks the target cell based on the value, and reprotects the sheet.
Standard Excel formulas cannot change cell properties like locked status, so VBA is required to achieve this automation.
The macro must temporarily unprotect the worksheet so it has the authority to change cell properties. Once the locked status is updated, it immediately reprotects the sheet to maintain data integrity.
Select the cells in your trigger column (e.g., column R). Right-click and choose 'Format Cells'. Go to the 'Protection' tab, uncheck 'Locked', and click OK to allow users to edit these cells when the sheet is protected.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. In the Project Explorer panel on the left, double-click the name of the worksheet where you want this behavior to occur.
Paste the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("R4:R1000")) Is Nothing Then Exit Sub Me.Unprotect "YourPassword" With Cells(Target.Row, Range("U1").Column) .Locked = (UCase$(CStr(Target.Value2)) <> "OPEN") End With Me.Protect "YourPassword" End Sub
Close the VBA editor. Type 'Open' in a cell within the trigger range (R4:R1000). You will now be able to edit the corresponding cell in column U. Typing anything else will instantly lock the column U cell.
Easily Manage Macros and VBA in WPS Spreadsheet
WPS Office provides robust support for VBA and macros in its premium versions, allowing you to automate tasks and conditionally lock cells exactly as you would in Microsoft Excel. Experience a familiar interface with high compatibility.
- 1. Download and Install: Get WPS Office from the official website and install it on your computer.
- 2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing your VBA code.
- 3. Enable Macros: Navigate to the 'Developer' tab and ensure macros are enabled to allow your background scripts to run seamlessly.
- 4. Open the VBA Editor: Click 'VBA Editor' in the Developer tab to view, edit, or write new conditional locking scripts.
Frequently Asked Questions
Why am I getting an error when the VBA code tries to lock the cell?
This typically occurs because the worksheet is protected, and the macro lacks permission to change the cell's 'Locked' property. Ensure your code includes 'ActiveSheet.Unprotect' (or 'Me.Unprotect') before changing the property, and 'ActiveSheet.Protect' immediately after.
Can I apply this conditional locking to multiple columns?
Yes. You can modify the VBA code to change the `.Locked` property for multiple cells. Inside the 'With' statement, you can reference multiple columns using `Offset` or by explicitly declaring the columns you wish to toggle.
Does the conditional locking work if I copy and paste multiple values at once?
A basic Worksheet_Change event might throw a mismatch error if you change multiple cells simultaneously (like pasting a range of data). To handle this gracefully, you should loop through each cell in the 'Target' range or add a condition to only execute when `Target.Count = 1`.
Will this VBA code run on Excel for Web or Mobile?
No. VBA macros are only supported in the desktop versions of Microsoft Excel and compatible desktop software like WPS Office. Web and mobile platforms do not support executing VBA scripts.
