seventy9mph
Board Regular
- Joined
- Apr 16, 2009
- Messages
- 72
here is my code, and I need help either combining the two Worksheet_Change routines or getting another way to do this. Thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$6:$F$8").ClearContents
Range("$F$4").Locked = False
Range("$F$5:$F$8").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").Locked = False
Range("$F$6").Locked = False
Range("$F$7").Locked = False
Range("$F$8").Locked = False
Range("$F$5").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").ClearContents
Range("$F$6:$F$8").ClearContents
Range("$F$4:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$P$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$6:$M$8").ClearContents
Range("$M$4").Locked = False
Range("$M$5:$M$8").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").Locked = False
Range("$M$6").Locked = False
Range("$M$7").Locked = False
Range("$M$8").Locked = False
Range("$M$5").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").ClearContents
Range("$M$6:$M$8").ClearContents
Range("$M$4:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$6:$F$8").ClearContents
Range("$F$4").Locked = False
Range("$F$5:$F$8").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").Locked = False
Range("$F$6").Locked = False
Range("$F$7").Locked = False
Range("$F$8").Locked = False
Range("$F$5").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").ClearContents
Range("$F$6:$F$8").ClearContents
Range("$F$4:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$P$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$6:$M$8").ClearContents
Range("$M$4").Locked = False
Range("$M$5:$M$8").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").Locked = False
Range("$M$6").Locked = False
Range("$M$7").Locked = False
Range("$M$8").Locked = False
Range("$M$5").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").ClearContents
Range("$M$6:$M$8").ClearContents
Range("$M$4:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub