VBA that enters TIMESTAMP after data entry, then locks it

[MichState]

I have a number of checks that are conducted with a user entering a 1=yes, 0=no in a number of cells when these checks are completed. How can I have a timestamp entered to the column left of entry and have it locked afterwards. I keep getting an error message when I lock the worksheet. File is attached with sample - no code.

[tfurnivall]

Take a look at this sample.

The trick, I think, is to UNLOCK the places where the initial "1" can be put, and then to protect the whole sheet.

You'll still need VBA to unlock the sheet before it makes the changes.

See what you think,

Tony

[MichState]

Thanks for the help. Below is an example of what code I was using. I attached another file showing how I want it to work - but I just need the time-stamped locked afterwards. Thanks.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B5"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

[tfurnivall]

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

Please Login or Register  to view this content.

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!

Please Login or Register  to view this content.

HTH

Tony

[tfurnivall]

Oh, and I fogot about your number format request. You've got the right code - it's just not in my example