Dynamically lock / unlock cells based on control cell value

Question

Hello: In Excel2016, is there a way to dynamically lock (non-editable, but visible) a range of cells, based on a control cell value?

eg..  Cell A1 is a drop down list ( Yes, No ) .  If A1 = "Yes" then A2..A9 = [locked] else [unlock] .

the intent is to force the user to physically set the A1 cell to "No" before an edit can occur on cells A2..A9. this worksheet will be unprotected. any advice would be greatly appreciated !

Answer 1

Hi,

Assuming that drop down menu list is in Sheet1, in cell A1

Since you are interesting, only for cells a2-a9 (lock-unlock- protect-unprotect)

if Yes  then [a1-a9: lock and  protect, rest area: unlock and unprotect] , else [unprotect and unlock all]

try this..

right click on Sheet1 tab, select View Code and paste in....

Private Sub Worksheet_Change(ByVal Target As Range)

'Mar. 26, 2016

Dim sPass

sPass = "123" '<< change password

Dim rng As Range

Set rng = [A2:A9]

If Not Intersect(Target, [A1]) Is Nothing Then

With ActiveSheet

.Unprotect Password:=sPass

.Cells.Locked = False

If Target.Value = "Yes" Then

rng.Locked = True

.Protect Password:=sPass

End If

End With

End If

End Sub