Solved: Protect and unprotect worksheet change

[khalid79m]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim OtherSh As Worksheet
     
    On Error GoTo ws_exit
     
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set OtherSh = ThisWorkbook.Sheets("Agent_View")
    If Target.Address = OtherSh.Range("D8").Address Then
        OtherSh.Range("H8").Value = ""
        'OtherSh.Unprotect = "54YY4F"
        OtherSh.Rows("18:47").ClearContents
        OtherSh.Rows("18:47").Interior.ColorIndex = 2
        'OtherSh.Protect = "54YY4F"
    ElseIf Target.Address = OtherSh.Range("H8").Address Then
        Run "Agent_View"
    ElseIf Target.Address = OtherSh.Range("C50").Address Then
        Run "Agent_View"
    End If
     
ws_exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
End Sub

My code fails on the password protect and unprotect .. what have I done wrong ?

[GTO]

Hi there,

Ditch the equal sign from both. Also, you were trying to change the value of H8 one line too early.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim OtherSh As Worksheet
     
    On Error GoTo ws_exit
     
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set OtherSh = ThisWorkbook.Sheets("Agent_View")
    If Target.Address = OtherSh.Range("D8").Address Then
        OtherSh.Unprotect "54YY4F"
        OtherSh.Range("H8").Value = ""
        OtherSh.Rows("18:47").ClearContents
        OtherSh.Rows("18:47").Interior.ColorIndex = 2
        OtherSh.Protect "54YY4F"
    ElseIf Target.Address = OtherSh.Range("H8").Address Then
        Run "Agent_View"
    ElseIf Target.Address = OtherSh.Range("C50").Address Then
        Run "Agent_View"
    End If
     
ws_exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
End Sub

[mdmackillop]

For neatness and efficiency, make user of With

[VBA]
Set OtherSh = ThisWorkbook.Sheets("Agent_View")
With OtherSh
If Target.Address = .Range("D8").Address Then
.Unprotect "54YY4F"
.Range("H8").Value = ""
.Rows("18:47").ClearContents
.Rows("18:47").Interior.ColorIndex = 2
.Protect "54YY4F"
ElseIf Target.Address = .Range("H8").Address Then
Run "Agent_View"
ElseIf Target.Address = .Range("C50").Address Then
Run "Agent_View"
End If
End With
[/VBA]