use DTPicker to select date and populate combobox

[smartphreak]

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

[royUK]

Filter for the date, copy & paste visible rows to empty sheet. Use that data as the rowsource

[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.

[smartphreak]

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

[royUK]

Please use Code Tags not Quote Tags

[royUK]

I use that method regularly so I know it works. Your code will not work because you have

"= & DateVal"

This should be

"="& DateVal

You don't need to select

I doubt if these work correctly

Range("b1", Range("b100").End(xlDown)).NAME = "GUESTRANGE"
 Range("b1", Range("f100").End(xlDown)).NAME = "myRANGE"