Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble 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.


arrow blue right bubbleRelated articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Christian Conti Gennaro · 6 months ago
    Hi, could you kindly check the reason why it doesn't work?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A3:A37").Value <> "" Then
    Range("B3:B37").Locked = True
    ElseIf Range("A3:A37") = "" Then
    Range("B3:B37").Locked = False
    End If

    If Range("B3:B37").Value <> "" Then
    Range("A3:A37").Locked = True
    ElseIf Range("B3:B37") = "" Then
    Range("A3:A37").Locked = False
    End If

    End Sub


    Thank you very much in advance!!!
  • To post as a guest, your comment is unpublished.
    Zk · 6 months ago
    Is it possible to lock a cell, when it reaches a certain value?
  • To post as a guest, your comment is unpublished.
    Mitchyll · 1 years ago
    How would the code be if I wanted to lock a block of cells (Rows 6, 7, and 8/Letters D through U as well as cells F5 and J5) and have them unlock when I put an "X" in cell E5? Thanks in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Hi MitchyII,
      Do you mean the specified block of cells have been locked manually in advance and just want to unlock them by typing an "X" in cell E5?
      If remove "X" from cell E5, you do want to lock the ranges again?
      I need more details of the problem.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Would you kindly advice me on how to correct this? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A:A") = "SLOW MOVING" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "OVER STOCK" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "NORMAL" Then
    Range("B:B").Locked = False
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      Noexpert · 1 years ago
      Not being a VB expert i would say you have too many "Elseif" - if you change them all to just IF except for the last one then hopefully that will work.
      Basically If X do this, If Y do this, if Z do this, if none of those - do this.
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Can you guide me on what's wrong here please? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("K:K") = "OVER STOCK" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "SLOW MOVING" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "NORMAL" Then
    Range("S:S").Locked = False
    ElseIf Range("K:K") = "SHORTAGE" Then
    Range("S:S").Locked = False
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    KB · 1 years ago
    I prepare a warehouse stock management in excel template.To deliver a stock i have to issue a gate pass.I want to each gate pass,corresponding data will be updated in daily stock take page.With the change of gate pass serial no,the row will be locked and next will be filled up.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Would be nice if you can upload your workbook here. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Andor Veres · 1 years ago
    Hi,
    I am really new to this.
    I have been trying to put an invoicing system together in excel.
    I created 3 sheets.
    1. Invoice template (Invoice)- Just a generic invoice that is sent to my agents weekly.

    2. A data sheet (Data sheet) to be exact - where the invoice can read the company name adress etc, so if anything changes the invoice will be automatically updated.

    3. A calendar tab (Calendar 2018) to be exact - that is referenced in the invoice template, and puts the corresponding date and invoice number on the actual invoices.

    What I want to do.
    The calendar tab would be my main page, I added a status drop down cell for each week with options "Active" and "Closed". I would like to Lock the whole "Invoice" tab if the corresponding cell is set to "Closed".

    I hope you guys understand what I am trying to do.
    Thanks in advance.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Ando Veres.
      The below VBA code can help you. Please place the code into the sheet code window of Calendar 2018 change A1 to your drop down cell. Thank you.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Target, Range("A1"))
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type >= 0 Then
      If Target.Value = "Closed" Then
      Sheets("Data Sheet").Protect
      ElseIf xRg.Value = "Active" Then
      Sheets("Data Sheet").Unprotect
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Mira · 1 years ago
    Hi! Can someone help me? I have to lock/freeze a cell. That cell is linked to another one and has a value which is changing every minute. What I what to do is to keep the value for a certain minute/ hour. How can I do that without copying it and paste it as a value?
  • To post as a guest, your comment is unpublished.
    Horace · 1 years ago
    can some one correct this pls>>>

    Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 7 To 100
    If Range("Cells(D, i)") = "Loan" Then
    Range("Cells(V, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "Savings" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(W, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "ShareCap" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(V, i)").Locked = True
    End If
    Next i
    End Sub
  • To post as a guest, your comment is unpublished.
    margie · 1 years ago
    Hi! need some advise.
    Is there a way not to allow a cell to be updated unless it has satisfied a condition on another cell?
    Sample: if the cell A is not updated it will not allow me to change the value of cell B to complete.

    Appreciate the feedback.
    Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Margie,
      Please try below VBA code.

      Dim PreVal As String
      Dim NextVal As String
      Private Sub Worksheet_Activate()
      PreVal = Range("A1")
      NextVal = Range("A1")
      End Sub
      Private Sub Worksheet_Change(ByVal Target As Range)
      If (Target.Count = 1) And (Target.Address = "$A$1") Then
      NextVal = Range("A1")
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = "$A$1" Then
      PreVal = Range("A1")
      ElseIf (Target.Address = "$B$1") Then
      If PreVal = NextVal Then
      Application.EnableEvents = False
      Range("A1").Select
      Application.EnableEvents = True
      End If
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    SHAINA · 1 years ago
    WHAT WILL BE THE CODE IF I WANT TO LOCK CELL E1, E2, E3 .............. FOR SPECIFIC TEXT (LETS SAY "P") ON CELL B1, B2, B3.................RESPECTIVELY.

    THANKS IN ADVANCE
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day
      Please try below VBA script.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = Range("A1").Address And Target.Value = "A" Then
      Range("B1").Locked = True
      ElseIf Target.Address = Range("A2").Address And Target.Value = "A" Then
      Range("B2").Locked = True
      ElseIf Target.Address = Range("A3").Address And Target.Value = "A" Then
      Range("B3").Locked = True
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Kristoffer · 1 years ago
    Hello,

    I have tried your code and edit a little bit, but i can't work out what i do wrong here?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A40") <> "" Then
    Range("D40:E40").Locked = False
    ElseIf Range("A40") = "" Then
    Range("D40:E40").Locked = True
    End If
    End Sub


    My though about it was if there is nothing in it (A40). Then i want to locked by VBA. If A40 contain something, then i want it to be unlocked. I hope you can see the sense of it.


    Regards Kristoffer
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      There is nothing wrong with your code. It works well for me.
      • To post as a guest, your comment is unpublished.
        KeeranB · 1 years ago
        Hi. I too cannot get this code to work. It does absolutely nothing. As if the code isn't even there?? I'm very new to VBA and have a basic understanding on it. Is this code being run as-is, or does it have to have stuff added to it as well for it to run? Or turned into a Macro (which I don't really get why because that's a recording of instructions, on my understanding of them)
  • To post as a guest, your comment is unpublished.
    leo · 2 years ago
    please can someone help me with the following.
    I want to insert pictures of student in one sheet, appears in another sheet based on their names
    To create a navigating plane to assist users
    To assign a particular sheet(s) to a user
    To create an interface for the workbook
    To create a login page
  • To post as a guest, your comment is unpublished.
    Ant · 2 years ago
    Hi, I'm trying to achieve this, but I get an error that VBA is unable to set the Locked property of the Range class if the sheet has been protected. Unprotecting the sheet will then negate the cell being locked.

    How to get around this?

    Thanks for any help.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Ant,
      The below VBA code can help you solve this probem. Thank you for your comment.

      Private Sub Worksheet_Activate()
      If Not ActiveSheet.ProtectContents Then
      Range("A1").Locked = False
      Range("B1:B4").Locked = False
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xRg As Range, xRgA As Range
      On Error Resume Next
      Application.EnableEvents = False
      Set xRg = Range("B1:B4")
      Set xRgA = Range("A1")
      If Intersect(Target, xRg).Address <> Target.Address _
      Or xRgA = "Accepting" Then
      Application.EnableEvents = True
      Exit Sub
      ElseIf ActiveSheet.ProtectContents _
      And Intersect(Target, xRg) = Target _
      And xRgA.Value = "Refusing" Then
      xRgA.Select
      End If
      Application.EnableEvents = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Christian · 2 years ago
        Hi,

        Is it possible to have this VBA lock one set of cells/unlock another based on this? For example Range B1:B4 is unlocked and C1:C4 is locked for "accepting" and then B1:B4 is locked and C1:C4 is unlocked for "refusing"?


        Thanks,
        Christian
        • To post as a guest, your comment is unpublished.
          crystal · 2 years ago
          Dear Christian,
          Is your worksheet protected?
    • To post as a guest, your comment is unpublished.
      Henry · 2 years ago
      You'll want to use the interface line in the workbook so when you open the file, it protects the sheets but allow macros to make changes anyway;

      Private Sub Workbook_Open() 'This goes into "ThisWorkbook"

      Worksheets("Order Tool").Protect Password:="Pwd", UserInterFaceOnly:=True

      End Sub
    • To post as a guest, your comment is unpublished.
      Memo · 2 years ago
      Did you resolved? I have the same problem
      • To post as a guest, your comment is unpublished.
        crystal · 2 years ago
        Dear Memo,
        Please try the below VBA code.

        Private Sub Worksheet_Activate()
        If Not ActiveSheet.ProtectContents Then
        Range("A1").Locked = False
        Range("B1:B4").Locked = False
        End If
        End Sub
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim xRg As Range, xRgA As Range
        On Error Resume Next
        Application.EnableEvents = False
        Set xRg = Range("B1:B4")
        Set xRgA = Range("A1")
        If Intersect(Target, xRg).Address <> Target.Address _
        Or xRgA = "Accepting" Then
        Application.EnableEvents = True
        Exit Sub
        ElseIf ActiveSheet.ProtectContents _
        And Intersect(Target, xRg) = Target _
        And xRgA.Value = "Refusing" Then
        xRgA.Select
        End If
        Application.EnableEvents = True
        End Sub
  • To post as a guest, your comment is unpublished.
    Sheetal Rao · 2 years ago
    How can lock/unlock a variable cell e.g. when cell is [=INDEX(A16:L35,MATCH(W5,A16:A35,0),MATCH("PAY",A16:L16,0))]
    • To post as a guest, your comment is unpublished.
      Mohammed Mandlaywala · 2 years ago
      I want a simple VBA command which I am unable to figure out please help
      If cell A1 is Balnk then Cell A2 is locked and If Cell A1 contains any Value then Cell A2 is unlocked
      Similarly if cell A2 is blank then Cell A3 is locked and if Cell A2 contains any value then Cell A3 is unlocked
      and so on as many cells as per requirement in any part of the sheet.