How do I set up Auotomatic date and time entry in an adjacent cell

G

Geoffrey

I want to set up a work activity log. It would be useful if the date and time
were automatically inserted in a cell adjacent to the activity as soon as the
type of activity is recorded, thus giving the start time. Any ideas?
 
P

Paul B

Geoffrey, here is one way,


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date & time in column B when something is put in A
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

To change the security settings go to tools, macro, security, security level
and set it to medium

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top