Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2006

Tip: Looking for answers? Try searching our database.

Making a time stamp

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rick - 20 Oct 2006 15:26 GMT
I need to make a column in a worksheet that will automatically enter the time
and date the info was entered in that row.. I have been playing with the
=NOW() function, but no luck so far.

Thanks!!
Bernie Deitrick - 20 Oct 2006 16:23 GMT
Rick,

Copy the code below, right click on the sheet tab, select "View Code", and paste the code into the
window that
appears.  You can change the B1:H1000 to a named range, a dynamic range, or any other range that you
want.... this will store the date of the change in column A.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Not Intersect(Target, Range("B1:H1000")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("B1:H1000"))
 Cells(myCell.Row, 1).Value = Date
Next
End If
Application.EnableEvents = True
End Sub

>I need to make a column in a worksheet that will automatically enter the time
> and date the info was entered in that row.. I have been playing with the
> =NOW() function, but no luck so far.
>
> Thanks!!
Sandy - 20 Oct 2006 16:47 GMT
Rick, Bernie's code will work great for you if you want to update the
date/time stamp for a row when you update any cell in that row. If you
don't want that to happen (have an original date/time stamp for when
the info was first entered) then here is Bernie's code modified. This
code will enter a date/time a stamp into column A only if it's blank.
Also, if you'd like the time along with the date, change Date value to
Now in the code and format accordingly.

hope this was helpful
Sandy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("B1:H1000")) Is Nothing Then
  If Cells(Target.Row, 1).Value = Empty Then
     Cells(Target.Row, 1).Value = Date
  End If
End If
Application.EnableEvents = True
End Sub

> I need to make a column in a worksheet that will automatically enter the time
> and date the info was entered in that row.. I have been playing with the
> =NOW() function, but no luck so far.
>
> Thanks!!
Rick - 21 Oct 2006 02:37 GMT
Thank you Bernie and Sandy!!!!! I have a use for both sets of code, and this
will help me out tremendously!! Thanks again!!

> Rick, Bernie's code will work great for you if you want to update the
> date/time stamp for a row when you update any cell in that row. If you
[quoted text clipped - 22 lines]
> >
> > Thanks!!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.