Skip to main content

How to lock or unlock cells based on values in another cell in Excel?

Author: Siluvia Last Modified: 2020-06-04

In some cases, you may need to lock or unlock cells based on values in another cell. For example, you need the range B1:B4 to be unlocked if cell A1 contains value “Accepting”; and to be locked if cell A1 contains value “Refusing”. How can you do to achieve it? This article can help you.

Lock or unlock cells based on values in another cell with VBA code


Lock or unlock cells based on values in another cell with VBA code

The following VBA code can help you lock or unlock cells based on value in another cell in Excel.

1. Right click the sheet tab (the sheet with cells you need to lock or unlock based on values in another cell), and click View Code from the right-clicking menu.

2. Then copy and paste the following VBA code into the Code window.

VBA code: Lock or unlock cells based on values in another cell

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") = "Accepting" Then
        Range("B1:B4").Locked = False
    ElseIf Range("A1") = "Refusing" Then
        Range("B1:B4").Locked = True
    End If
End Sub

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

From now on, when you enter value “Accepting” into cell A1, the range B1:B4 is unlocked.

When entering value “Refusing” into cell A1, the specified range B1:B4 is locked automatically.


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (57)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi
How to lock all cells with "O" in area AH4:AE370
This comment was minimized by the moderator on the site
How do I automatically protect a row based on a cell?
Example: if you enter a value in cell E1, it will automatically protect the cells to the left of the row, D1, C1, B1 and A1
And the same would apply to the following lines, if you enter a value in cell E2 it will automatically protect the cells to the left and so on.

Please help me.
This comment was minimized by the moderator on the site
I need an automatic command that whenever I put something in cell g3, g4, g5 .... it automatically protects the data in the cells on the left ...
example: if you insert data in cell g3, it will automatically protect cells F3, E3, D3, C3, B3, A3... could you help me?


Today I can only save this way by creating a button, and it is very manual and time-consuming...
This comment was minimized by the moderator on the site
there is this command that protects an entire line, but I can't think of anything to solve my case and automate the protection

If Target.Row = 1 And Target.Value <> "" Then
This comment was minimized by the moderator on the site
Hello, Is there any way to have this repeat down an entire sheet. I have this code entered:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("X3") = "N0" Then
Range("B3:W3").Locked = False
ElseIf Range("X3") = "Yes" Then
Range("B3:W3").Locked = True
End If
End Sub

This is working fine for that row but I would like it to apply to each row as the value "Yes" is selected in column X. Is this possible and if so how do I do that? 
Thanks! 

This comment was minimized by the moderator on the site
Hi Stephanie,
Do you mean that when Yes is selected in each cell of column X, the corresponding row will be locked? When switching to No, the corresponding row will be unlocked?Or just select Yes in any cell of column X to lock all rows in the current worksheet?
This comment was minimized by the moderator on the site
Yes, do you know how?
This comment was minimized by the moderator on the site
I want to allow only 2 cells to edit as L after the two edits remaining cells should lock in that column can it possible ?
This comment was minimized by the moderator on the site
Hi Venkat,You can see if there is an answer you need in this article.
https://www.extendoffice.com/documents/excel/3778-excel-lock-cell-after-data-entry-input.html
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations