Time stamp...

[mad3]

I want to be able to have a user place an 'X' in a cell and then the cell is timestamped with the time the X was placed. I am using this in another formula to track time.

Thanks,

Mike from California...

 

[Smitty]

Welcome to the Board!

This will put a date stamp in column B when an X is entered in column A:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> UCase(Target.Value) = "X" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "X" Then
    Application.EnableEvents = False
    Target.Value = Now
    Application.EnableEvents = True
End If
End Sub

 

[mad3]

Thanks for the information. It worked fine, until I made a change to add a column then it stopped working.

What caused it to stop working?

Column A has category informatioin
Column B has subcategory nformation
Column C has the timestamp (This stopped listing time when I added column D)
Column D is where I am going to calculate time spend on the various categories.

 

[VoG]

Can you post the code that doesn't work.

 

[mad3]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "X" Then
Application.EnableEvents = False
Target.Value = Now
Application.EnableEvents = True
End If
End Sub


This is the code I used. It worked until I made a modification to the worksheet then it stopped (re)placing the 'X' with the time.

 

[VoG]

Try this: in the code window press CTRL + G to open the Immediate Window and in that window type or paste

Application.EnableEvents = True

and press Enter. Then try again.

 

[mad3]

Thanks again for the information...All worked well.