Skip to main content

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

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

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/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   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
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 Toolset:  12 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, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 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!
Comments (55)
No ratings yet. Be the first to rate!
Monteiro
Monteiro
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.
Reply
0
0
Monteiro
Monteiro
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...
Reply
0
0
Monteiro
Monteiro     Monteiro
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
Reply
0
0
Stephanie
Stephanie
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! 

Reply
0
0
crystal
crystal     Stephanie
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?
Reply
Report
0
0
Venkat
Venkat
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 ?
Reply
0
0
crystal
crystal     Venkat
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
Reply
Report
0
0
Belcrai
Belcrai
This comment was minimized by the moderator on the site
I'm using this fine to lock Cells B-D when the value in A is entered. I need this to be replicated for almost 500 rows in 8 sheets. is there a way to do this without having to type 400 lines on code? i.e. if the value in a = "accepted" then columns b-D are locked for that row only
Reply
Report
0
0
Maz
Maz
This comment was minimized by the moderator on the site
The code does not do anything when I tried it. Does this work on MS Office Professional 2010?
Thanks.
Reply
0
0
crystal
crystal     Maz
This comment was minimized by the moderator on the site
Hi Maz,The code has been successfully tested in Excel 2010.
Reply
Report
0
0
Jeffrey Scott
Jeffrey Scott
This comment was minimized by the moderator on the site
If my first column has strings can I lock that row specifically to the first column, where when I do a sort the full row will stay together on the sort?
Reply
0
0
crystal
crystal     Jeffrey Scott
This comment was minimized by the moderator on the site
Good day,Sorry can't help you for that.
Reply
Report
0
0
Steffi
Steffi
This comment was minimized by the moderator on the site
Hello,

I need your favour. when i select No option from a cell i want the columns below to be locked/greyed out.

Is that possible? I tried this formula but dint work

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("D90") = "Yes" Then

Range("C94:F104").Locked = False

ElseIf Range("D90") = "No" Then

Range("C94:F104").Locked = True

End If

End Sub
Reply
0
0
crystal
crystal     Steffi
This comment was minimized by the moderator on the site
Hi Steffi,
The code works well.
If you want to lock the range of cells to prevent editing, you need to protect the worksheet manually after selecting No option in D90.
And as D90 is in the range C94:F104, after protecting the worksheet, D90 can't be edited either.
Reply
Report
0
0
Mike
Mike     crystal
This comment was minimized by the moderator on the site
D90 is not in the range. It's 4 rows above C94
Reply
0
0
Kyle
Kyle
This comment was minimized by the moderator on the site
I copied everything from above into a sheet. It worked for a second and now I'm getting the error 'Unable to set the Locked property of the Range class'. I even opened a whole new sheet and copied your example verbatim. Any idea whats going on?
Reply
0
0
satya
satya
This comment was minimized by the moderator on the site
Hi please any one help me for VBA code .

if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)



if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)



if i select from drop down in cell Range(A1:A1000) "abc" then lock Range(D1:D1000) to (F1:F1000)



i.e respective cell A1 for D1 to F1



A2 for for D2 to F2
Reply
0
0
benthe
benthe
This comment was minimized by the moderator on the site
hi

if I need to lock a cell on sheet 2 (cell C4) based on a claim from sheet 1 (cell C1),
if "no" on sheet 1 C4; sheet 2 must be locked and it must transfer the value from sheet 1 C4,
to sheet 2 C4.

if "yes" on sheet 1, I must be able to type in the cell on sheet 2

Thanks Benthe
Reply
Report
0
0
Chandrakanth
Chandrakanth
This comment was minimized by the moderator on the site
Hi
I have just tried using the code above
and it says type 13 error when I try to use it.
could you help me out with this?

thank you
Reply
0
0
crystal
crystal     Chandrakanth
This comment was minimized by the moderator on the site
Hi,
Which Excel version are you using?
Reply
Report
0
0
cricontidj@gmail.com
This comment was minimized by the moderator on the site
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!!!
Reply
Report
0
0
Zk
Zk
This comment was minimized by the moderator on the site
Is it possible to lock a cell, when it reaches a certain value?
Reply
0
0
mfuller73
mfuller73
This comment was minimized by the moderator on the site
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!
Reply
Report
0
0
crystal
crystal     mfuller73
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Jennifer Cornell
Jennifer Cornell     crystal
This comment was minimized by the moderator on the site
Hi Mitchyll (or anyone), building off of Crystal. I have manually locked all cells and based on the information in column G, I would like them to remain locked or unlock. Example cells in column H should ONLY be unlocked if "c/p" is placed in the preceding cell in column G
Reply
0
0
sarcotec
sarcotec
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Noexpert
Noexpert     sarcotec
This comment was minimized by the moderator on the site
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.
Reply
0
0
Joe
Joe
This comment was minimized by the moderator on the site
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
Reply
0
0
KB
KB
This comment was minimized by the moderator on the site
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.
Reply
0
0
crystal
crystal     KB
This comment was minimized by the moderator on the site
Good Day,
Would be nice if you can upload your workbook here. Thank you for your comment.
Reply
Report
0
0
veandor@gmail.com
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
crystal
crystal    
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Mira
Mira
This comment was minimized by the moderator on the site
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?
Reply
0
0
crystal
crystal     Mira
This comment was minimized by the moderator on the site
Dear Mira,
Sorry can’t help with this, you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
Reply
Report
0
0
Horace
Horace
This comment was minimized by the moderator on the site
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
Reply
0
0
margie
margie
This comment was minimized by the moderator on the site
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!
Reply
0
0
crystal
crystal     margie
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
SHAINA
SHAINA
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     SHAINA
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Ghost
Ghost     crystal
This comment was minimized by the moderator on the site
Добрый день!
Мне тоже необходимо заблокировать значение, но только в одной ячейке в зависимости от значения в другой, я попробовала использовать код в комментарии выше, но он не работает(оставила только один параметр для блокировки), но он у меня не работает - при этом нет сообщения об ошибке, ячейка, которая должна быть заблокирована - просто не блокируется, остается активной. В чем может быть причина?

Вот код, который я использовала:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Coun t=1 Then
If Target.Address = Range("C9").Address And Target.Value = "согласно плану" Then
Range("C10").Locked = True
End If
End Sub
Reply
0
0
crystal
crystal     Ghost
This comment was minimized by the moderator on the site
Hi Ghost,
I am sorry I don't quite understand what you mean. For clarity, please attach a sample file or a screenshot with your data and desired results.
Reply
Report
0
0
Kristoffer
Kristoffer
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     Kristoffer
This comment was minimized by the moderator on the site
Good Day,
There is nothing wrong with your code. It works well for me.
Reply
Report
0
0
KeeranB
KeeranB     crystal
This comment was minimized by the moderator on the site
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)
Reply
0
0
leo
leo
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     leo
This comment was minimized by the moderator on the site
Dear leo,
Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
You will get more supports about Excel from our Excel professional.
Reply
Report
0
0
Ant
Ant
This comment was minimized by the moderator on the site
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.
Reply
0
0
crystal
crystal     Ant
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Christian
Christian     crystal
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     Christian
This comment was minimized by the moderator on the site
Dear Christian,
Is your worksheet protected?
Reply
Report
0
0
Henry
Henry     Ant
This comment was minimized by the moderator on the site
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
Reply
0
0
Memo
Memo     Ant
This comment was minimized by the moderator on the site
Did you resolved? I have the same problem
Reply
0
0
crystal
crystal     Memo
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Sheetal Rao
Sheetal Rao
This comment was minimized by the moderator on the site
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))]
Reply
0
0
Mohammed Mandlaywala
Mohammed Mandlaywala     Sheetal Rao
This comment was minimized by the moderator on the site
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.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations