Quick Date / Text query

  • Thread starter Thread starter Mathew P Bennett
  • Start date Start date
M

Mathew P Bennett

Good evening all,
This hopefully will be simple:

I have in cell $A$1, =TODAY() (Formatted as dd/mm/yyyy)

but also use $A$1,(&$A$2) as a list for data verification, so that in the drop-down box, one chooses
from either "02/12/2003" or "". [""=($A$2)]

My question being, is it possible to have "Today", rather than "02/12/2003", appearing in the drop-down box?

Us usual suggestions welcome.
Cheers,
Mathew
 
Whatever is in cell A1 will appear at the top of the validation list. So if
you put "Today" in A1 that's what the user will see in the list. Now if you
want Excel to magically convert the text "Today" to the current date when
the user picks Today from the validation list that will take a macro. Use
the change event with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("DateCell").Address Then
If LCase(Target.Value) = "today" Then _
Target.Value = Format(Now, "dd/mm/yy")
End If
End Sub

This assumes that the cell with the validation is named "DateCell". If the
user tries to type in the date rather than pick from the list that would
fail because Excel is looking for "today".
 
Hi Jim
Thank you very much for the code.
I will have a play with it.
Cheers again.
Mathew
Whatever is in cell A1 will appear at the top of the validation list. So if
you put "Today" in A1 that's what the user will see in the list. Now if you
want Excel to magically convert the text "Today" to the current date when
the user picks Today from the validation list that will take a macro. Use
the change event with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("DateCell").Address Then
If LCase(Target.Value) = "today" Then _
Target.Value = Format(Now, "dd/mm/yy")
End If
End Sub

This assumes that the cell with the validation is named "DateCell". If the
user tries to type in the date rather than pick from the list that would
fail because Excel is looking for "today".
 
Back
Top