A couple of quick questions.
The only cell you test for is B5. Are ALL of your test going to use B5 as the response point? If so, how will you know which test is being signed off? (The only cell you modify is "A5"!)
You need to Unprotect the worksheet in order to make the changes (a locked cell is only protected when the worksheet is protected), and then re-protect it when you are done (see the lines in my code).
As I mentioned, I've added a column so that you can get a description of the step, however, if you don't want that, then adjust the column references accordingly.
If you don't want to lock the sign-off cell (is it of any use if people can change it?) then simply comment out the
line.
Lastly, try and get into the habit of using .Formula rather than .Value when setting the contents of a cell. It's the way Bill and Steve prefer it!
Private Sub Worksheet_Change(ByVal Target As Range)
' See if we are trying to change a checkable cell
ActiveSheet.Unprotect
If Target.Column = 3 Then
Application.EnableEvents = False
If Target.Value = 1 Then
Target.Offset(0, -1).Formula = Now()
Else
Target.Offset(0, -1).Formula = ""
End If
Target.Locked = True
Target.Offset(0, -1).Locked = True
Application.EnableEvents = True
Else
End If
ActiveSheet.Protect userinterfaceonly:=True
End Sub
HTH
Tony
Bookmarks