JE:
I typed in the code behind the combo box as instructed, but it doesn't work.
When I move my cursor over the box, the little four-sided black arrows appear
and I can't click into it. Also, does the A1 in the code (Private Sub
ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need
to keep that list? I'm not a programmer as I'm sure you've been able to
devise. . . . Help!

"JE McGimpsey" wrote:

AFAIK, there's no way to get the validation list to pop up in the middle
of the list.

One workaround would be to create a Combobox from the control toolbox on
the worksheet that would dynamically load the FY's dates, and preselect
today's date:

Enter this code behind it (right-click the combobox and choose View
Code):

Private Sub ComboBox1_Gotfocus()
Dim dDates() As Date
Dim nFY As Long
Dim i As Long

nFY = Year(Date + 92)
ReDim dDates(0 to DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0))
dDates(0) = DateSerial(nFY - 1, 10, 1)
For i = 1 To UBound(dDates)
dDates(i) = dDates(0) + i
Next i
With ComboBox1
.List = dDates
.ListIndex = Date - dDates(0)
End With
End Sub

Private Sub ComboBox1_Click()
Range("A1").Value = ComboBox1.Value
End Sub

In article ,
"Jan Buckley" wrote:

JE: I was thrilled when, using your instructions, I got this to work.
However, I now find that ,not only can I not back up in the list to a date
prior to TODAY(), neither can I type in a previous date. Since there are
times when it will be necessary to enter an earlier date (previous to
TODAY()), is there a solution to this? I sure hope so - setting the date to
the current date is very convenient and a time saver, but I won't be able to
use it if I can't 'back up'. Thanks so much.
Jan