Unlock Locked Cell based on another Cell Value

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19
I have a spread sheet that is Locked/Protected. I want to be able to unlock cell D10 based on the Value of cell C10. so if cell C10 ="Other" I want cell D10 to unlock. else then Cell D10 to remain Lock.

thank You in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152
Hi,

Right-click on the Worksheet tab of your Protected Sheet and click view code.

Paste the following code in the vba editor that appears :

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
    Range("D10").Locked = Range("C10") <> "Other"

End Sub
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,374
Office Version
  1. 365
Platform
  1. Windows
You may need to add to your code some line that unprotect the sheet first before making changes to the locked statuses of cells and then re-protect it again at the end of the code.
 
Last edited:

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19
louisH

Thank You, However when I enter Other in cell C10 and select cell D10 to enter text I receive a run time error stating " Unable to set the locked property of the range class.
Thank You, However when I enter Other in cell C10 and select cell D10 to enter text I receive a run time error stating " Unable to set the locked property of the range class."
 
Last edited:

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152

ADVERTISEMENT

Yes my bad. As Joe4 stated you have to change your code like this :

Remove the "YourPassWord" if you don't have one

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
ActiveSheet.Unprotect "YourPassWord"

    Range("D10").Locked = Range("C10") <> "Other"

ActiveSheet.Protect Password:="YourPassWord"

End Sub
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,374
Office Version
  1. 365
Platform
  1. Windows
Try this version:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Count > 1) Or (Target.Address(0, 0) <> "C10") Then Exit Sub
    
    ActiveSheet.Unprotect Password:="password"
    Range("D10").Locked = (Range("C10") <> "Other")
    ActiveSheet.Protect Password:="password"

End Sub

Louis, I don't think the part in red is doing what you think:
Code:
    If Target.Count > 1 Or [COLOR=#ff0000]Target <> Range("C10")[/COLOR] Then Exit Sub
That is not checking for the location or address of the Target cell being updating. What that is really asking is if the VALUE in the Target cell is NOT equal to the VALUE of Range("C10"). So, if some other cell is being updated (like say Z1), and the value in Z1 does not equal the value in cell C10, it is going to cause the succeeding lines of code to needlessly run/be evaluated.
 
Last edited:

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19

ADVERTISEMENT

Yes my bad. As Joe4 stated you have to change your code like this :

Remove the "YourPassWord" if you don't have one

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
ActiveSheet.Unprotect "YourPassWord"

    Range("D10").Locked = Range("C10") <> "Other"

ActiveSheet.Protect Password:="YourPassWord"

End Sub

Great this worked. do you know how I can add to the code if Other is not entered in cell C10 to delete whatever the user entered in cell D10?
 

kiwikiki718

New Member
Joined
Apr 7, 2017
Messages
19
Try this version:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Count > 1) Or (Target.Address(0, 0) <> "C10") Then Exit Sub
    
    ActiveSheet.Unprotect Password:="password"
    Range("D10").Locked = (Range("C10") <> "Other")
    ActiveSheet.Protect Password:="password"

End Sub

Louis, I don't think the part in red is doing what you think:
Code:
    If Target.Count > 1 Or [COLOR=#ff0000]Target <> Range("C10")[/COLOR] Then Exit Sub
That is not checking for the location or address of the Target cell being updating. What that is really asking is if the VALUE in the Target cell is NOT equal to the VALUE of Range("C10"). So, if some other cell is being updated (like say Z1), and the value in Z1 does not equal the value in cell C10, it is going to cause the succeeding lines of code to needlessly run/be evaluated.

Joe thanks for your help.
 

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152
Thank you @Joe4 I forgot about that...

This will clear D10 :

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Address <> Range("C10").Address Then Exit Sub
    
    ActiveSheet.Unprotect "YourPassWord"
    
    If Range("C10").Value = "Other" Then
        Range("D10").Locked = False
    Else
        Range("D10").ClearContents
        Range("D10").Locked = True
    End If
    
    ActiveSheet.Protect Password:="YourPassWord"
End Sub
 
Last edited:

Forum statistics

Threads
1,136,951
Messages
5,678,743
Members
419,782
Latest member
gc75150

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