use DTPicker to select date and populate combobox

Hi All,

I have a worksheet with dates in Column A and Names in Column B.

Using a UserForm I want to be able to use the DTPicker to select a date and then using that value populate my combobox with possible names, in alphabetical order...

Any ideas - i vaugly remember something about Array and a loop of sorts... not sure if this is the most efficient way.

Any ideas for me to cunch out?

Thanks,
smartphreak

Hi Roy,

Unfortunately no...

I am running a UserForm.... it woudl not make sense to do that for every enquiry. There should be an easier way. ODBC perhaps?

Just not sure of the coding.

Hi there,

Trying your idea.... this is the code i have so far

Private Sub DTPickerD_Change()

Dim GUESTRANGE As Range
Dim DateVal As Variant
Dim nRow As Integer
DateVal = Me.DTPickerD.Value

Sheets("DATA").Select
Selection.AutoFilter
ActiveSheet.Range("A1:F100").AutoFilter Field:=1, Criteria1:= _
"= & DateVal", Operator:=xlAnd

Sheets("DATA").Range("A2:F100").Copy
Sheets("Filter").Select
Range("A1").Select
ActiveCell.PasteSpecial xlPasteAll
Range("b1", Range("b100").End(xlDown)).NAME = "GUESTRANGE"
Range("b1", Range("f100").End(xlDown)).NAME = "myRANGE"

Me.ComboBoxGuests.RowSource = "GUESTRANGE"
End Sub

This works when i substitute the "= & DateVal", in filter for an actual date ie "=11/11/2011" but i need the date to be dynamic so that when i select it from the DTPicker can populate the filter.

Any thoughts?
Thanks - smartphreak