Click cell in Excel XP for auto Date/Time 2

[MAGICofSeth]

I need a column of cells that when I click on a cell, the current date and time is entered into that cell. When I click the next cell down (on another day), the current date and time would be automatically entered into that cell, and so on. I keep ending up with both cells getting the same dates or updating themselves with the current time every time I enter a new date/time.

I think I need some kind of VBA code written, although it sounds simple enough, I can't seem to do get it right. Some kind of "on click" command that puts the current date and time into the cell. Then have that apply to all cells in column A from (a9 to a9999).

If anyone can help... PLEASE let me know!
Thank You!
--Seth

 

[PHV]

No onClick event in Excel.
You may consider the double click one:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A9:A9999")) Is Nothing Then
Target.Value = Now
Cancel = True
End If
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MAGICofSeth]

Thank you so much! Double clicking is perfect. I am having trouble with puting the code in the correct spot. Would you please take the time to explain to me- what to do with the code you wrote.

Thank you again so much for your time! I can't wait to use it!

--Seth

 

[PHV]

Go in VBE (Alt-F11)
Display the project browser (Ctrl-R)
Double-Click the name of the sheet of interest to open the module window.
In the left combo, choose Worksheet
In the right combo, choose BeforeDoubleClick
Copy and paste the If ... End If block of code I posted.
Return to the worksheet (Alt-F11)
Double click a Cell you want to be updated and another one to be sure the test is OK.
Let me know if it works like you wanted or not.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MAGICofSeth]

I just completed your instructions for each worksheet and it works perfectly!! I could not have asked for a better outcome. It is EXACTLY what I needed. Thank you so much! I REALLY appreciate your help and time. Great Job!

Thanks again--

Seth

 

[xlbo]

Seth - just a note to say that whilst I'm sure PHV is happy enough with your praise, there is also a link to add a star to the thread (Thank PHV for this valuable post!)

- this is the TT way of saying thanks but, it also highlights threads where useful / helpful / expert advice has been given - this then helps those users who search the archives here for help. I have awarded a star to PHV on your behalf as it seems you were more than happy with the solution provided. Hope this is ok and please don't take this as a lecture - purely a tip as it seems you are fairly new to the site

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MAGICofSeth]

Thank you. I have marked the messages with a star. I appreciate you letting me know how to correctly give praise in this forum...

Last Entry-
Seth