time stamp

[jiwolf]

Anybody know how to time stamp a file? I need to execute a macro to insert
the date and time into a cell ( say cell A1). This cell has then to be
locked until a password is entered.

Any help greatly appreciated



Pete

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "drowssap"
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
.Parent.Unprotect Password:=sPWORD
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
.Locked = True
.Parent.Protect Password:=sPWORD
Application.EnableEvents = True
End If
End With
End Sub




See

http://www.mcgimpsey.com/excel/timestamp.html

for additional ideas.

 

[jiwolf]

thanks for the code, but how do I run the macro?

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "drowssap"
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
.Parent.Unprotect Password:=sPWORD
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
.Locked = True
.Parent.Protect Password:=sPWORD
Application.EnableEvents = True
End If
End With
End Sub




See

http://www.mcgimpsey.com/excel/timestamp.html

for additional ideas.

 

[Dave Peterson]

You don't explicitly run it. It's an event macro that's always hanging around
waiting for you to change something in A2:A10.

If you didn't follow J.E.'s instructions (putting the code behind the
worksheet), then move the code there.

If you did follow his instructions and the event doesn't fire, then make sure
you open excel with macros enabled.

Or maybe you just have to change the range from A2:A10 to something else???

 

[jiwolf]

ah, doh!

You don't explicitly run it. It's an event macro that's always hanging
around
waiting for you to change something in A2:A10.

If you didn't follow J.E.'s instructions (putting the code behind the
worksheet), then move the code there.

If you did follow his instructions and the event doesn't fire, then make
sure
you open excel with macros enabled.

Or maybe you just have to change the range from A2:A10 to something
else???