Insert date time in another cell when dropdown selection is made.

[Gary Rickert]

I have a dropdown list that reflects the status of a task. I would like to capture the date and time the selection is made, ie the status changes. Been trying for days and can't find a solution. Pls help.

 

[Frank Kabel]

Hi
you'll need VBA for this (using the worksheet_change event). See:
http://www.mcgimpsey.com/excel/timestamp.html

Note: If you're using Excel 97 the worksheet_change event is nOT
triggered by a change in a data validation dropdown box. In this case
you have to use for example the following workaround:
- use a formula in a separate filed whcih just references the field
with your drop_down
- use the worksheet_calculate event

 

[Gary Rickert]

Thanks a lot Frank. As an FYI, I posted this question on another site and received the following script which also works. I have over 300 books to copy the script into. Such fun.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range, Cel As Range, cel1 As Range
Dim strFormat As String
Application.Volatile True
Set rg = Intersect(Target, Range("G6:G6")) 'checks if the cell In column C is changed
If rg Is Nothing Then Exit Sub 'checks if the value is not empty
For Each Cel In rg.Cells
If Cel.Value <> "" Then
Cel.Offset(-1, 0).Value = Now() 'places the current date into it
End If
Next Cel
End Sub