Unlocking/Locking cells based on value of another cell

gatorbait

New Member
Joined
Nov 25, 2008
Messages
36
I'm trying to lock or unlock cells based on the value of another cell, but I keep running into an error and need some assistance.

I've created a spreadsheet with a data validation list in column J. When the value in column J is "Default", the adjacent cells K-M are locked. If the user needs to change the default values in K-M, the user can unlock these cells by changing the drop down list in column J to "Override Values".

I'm using a custom data validation for columns K-M, =$J$17:$J$64<>"Default". The cells are being locked/unlocked using the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J17") = "Override Credit %" Then
Range("K17:M64").Locked = False
ElseIf Range("J17") = "Default" Then
Range("K17:M64").Locked = True
End If
End Sub

Everything works when there is no other data in the spreadsheet, but as soon as I enter data and try to change a value in columns K-M I get the following error:

"This value doesn't match the data validation restrictions defined for this cell".

I can't seem to figure out the why. Can anyone tell me what I'm doing wrong?

Thanks in advance for your assistance.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
Are ALL rows looking at cell J17, or do you want each individual row to look at column J in that particular row, i.e.
- K17-M17 will look at J17
- K18-M18 will look at J18
- K19-M19 will look at J19
etc

Also, I am not sure what you are trying to do with this:
I'm using a custom data validation for columns K-M, =$J$17:$J$64<>"Default".
You use Data Validation to limit the possible entries in those cells (i.e. a list of possible values they can select from).
It does not work like Conditional Formatting.
If I understand your question, I don't think it is necessary to what you are trying to do here.
 

gatorbait

New Member
Joined
Nov 25, 2008
Messages
36
Are ALL rows looking at cell J17, or do you want each individual row to look at column J in that particular row, i.e.
- K17-M17 will look at J17
- K18-M18 will look at J18
- K19-M19 will look at J19
etc

Also, I am not sure what you are trying to do with this:

You use Data Validation to limit the possible entries in those cells (i.e. a list of possible values they can select from).
It does not work like Conditional Formatting.
If I understand your question, I don't think it is necessary to what you are trying to do here.
Hi Joe4, thanks for your assistance. Each row should be evaluated on it's own, so your example (K17-M17 will look at J17...) is correct.

I tested removing the custom data validation from K-M, but the cells become unlocked regardless of the value in J.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
Also note that locking/unlocking cells does nothing without sheet protection (can do it with or without a password).
All cells are, by default, locked. So if you turn on sheet protection, ALL cells will be locked.
Typically what people do is highlight the whole sheet, and then unlock the cells, then protect the sheet. Then the code should do what you want.

Here is the code I came up with for you. I added lots of comments (in green) to explain what those steps are doing.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim rw As Long
   
'   See if any cells updated in column J, if not exit
    Set rng = Intersect(Target, Range("J17:J64"))
    If rng Is Nothing Then Exit Sub
   
'   Loop through values in column J just updated
    For Each cell In rng
'       Get row number
        rw = cell.Row
'       Unprotect sheet
        ActiveSheet.Unprotect
'       Check value
        Select Case cell.Value
            Case "Override Credit %"
                Range("K" & rw & ":M" & rw).Locked = False
            Case "Default"
                Range("K" & rw & ":M" & rw).Locked = True
        End Select
'       Protect sheet
        ActiveSheet.Protect
    Next cell

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,313
Members
414,375
Latest member
Onmyown

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top