PDA

View Full Version : Solved: Protect and unprotect worksheet change



khalid79m
04-20-2010, 02:55 AM
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
04-20-2010, 04:04 AM
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
04-20-2010, 04:27 AM
For neatness and efficiency, make user of With


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