CODEWITHSUNDEEP
excelvba
Filip Frątczak
Filip Frątczak

Reputation: 157

Dynamically locked cell in not locked range

In my worksheet, I have locked whole sheet, with password, with not locked range.

Not locked range is =A14:H50 and I want to make range =C14:C50 become dynamically locked range based on BX value. Eg. If B14 value locked, C14 should be locked. If B14 value notlocked, C14 shouldn't be locked.

There are many codes for dynamically locked cells, but I don't know how it works if whole sheet without range is locked also.

Upvotes: 0

Views: 66

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Please, copy the next event code in the sheet code module. The code as it is, protect and unprotect without a password. If your sheet is protected with a password, please change the line pass = "" in pass = "myPassword":

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B14:B50")) Is Nothing Then
        Dim pass As String
        pass = "" 'set the password. Otherwise, protection/unprotection is done without a pass
        If Target.Cells.Count > 1 Then Exit Sub
        ActiveSheet.Unprotect pass
        If Target.Value = "locked" Then
            Target.Offset(0, 1).Locked = True
        ElseIf Target.Value = "notlocked" Then
            Target.Offset(0, 1).Locked = True
        End If
        ActiveSheet.Protect pass
    End If
End Sub

Upvotes: 1

Related Questions