CODEWITHSUNDEEP
excelvba
sheetal singh
sheetal singh

Reputation: 119

Lock/unlock a variable cell(s)

My Excel VBA unlocks the desired cells but can not relock when criteria changes.

In my Excel utility, if A1 has value "A", then it will find B1 in the range A8:A13 and unlock the cells of the row having value of B1 and column having value "b" and column having value "c" (Here cells B9 and C9 to be unlocked). My VBA works here but when I change the value of B1 (e.g. "w" to "e" it again unlock the corresponding cells e.g. cells B10 and C10 but it does not relock the cells unlocked earlier i.e. cells B9 and C9. They remains unlocked.

Excel utility

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] = "A" Then
    ActiveSheet.Unprotect ("")
    [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("b",A8:F8,0))].Locked = False
    [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("c",A8:F8,0))].Locked = False
    ActiveSheet.Protect ("")
Else
    ActiveSheet.Unprotect ("")
    [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("b",A8:F8,0))].Locked = True
    [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("c",A8:F8,0))].Locked = True
    ActiveSheet.Protect ("")
End If
End Sub

Upvotes: 0

Views: 502

Answers (1)

user3598756
user3598756

Reputation: 29421

change code to this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If [A1] = "A" Then
        Unprotect ""
        Range("B9:F13").Locked = True '<--|| set all range "locked" 
        [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("b",A8:F8,0))].Locked = False '<--| unlock wanted cell
        [=INDEX(A8:F13,MATCH(B1,A8:A13,0),MATCH("c",A8:F8,0))].Locked = False '<--| unlock wanted cell
        Protect ""
    End If
End Sub

Upvotes: 1

Related Questions