Hello, I have a macro that works well in which the user selects an option from a drop down menu and it protects the row from any more changes by other users. However, in this process, the sheets gets unprotected and protected in the background. The problem is that when it gets re-protected, it looses the options i had previously chosen (autofiltering and inserting rows) - how can i add that to the script?
Private Sub worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("v5:v1000")) Is Nothing Then
If Target.Value = "Not seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = False
Me.Protect Password:="123"
ElseIf Target = "Seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"
ElseIf Target = "Imported to Signal" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"
End If
End If
End Sub
Private Sub worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("v5:v1000")) Is Nothing Then
If Target.Value = "Not seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = False
Me.Protect Password:="123"
ElseIf Target = "Seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"
ElseIf Target = "Imported to Signal" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"
End If
End If
End Sub