Changing Combobox for valid dates from a list

[Nitzan]

Hi there,

I would like to give a user the option to pick a date from a list (without seeing it).
I would like to do it with 3 combobox so that user can pick separately year, month and day but only for what valid (in the list).

for example, for the dates 1/1/2006-15/2/2006:
1. user picks combobox for year: 2006
2. user picks combobox for month: 1 or 2
3. user picks combobox for day: 1-31 (for month 1) or 1-15 (for month 2, which means he can't even see 16-31 in that combox)

To be clear - there can be a case when 8/1/2006 is not on the list so when you pick 2006 and then 1 you don't see the 8 option in the combobox.

how can I do that?
is it necessary to use VBA or can I do it more simple?

Thank you!

[xLJer]

The attached workbook was done with Excel 2003. It has
a form that includes 3 combo boxes to select year, month and day.
Try it out and see if it can help you with your solution.
It works off a list of dates listed in chronological order in Sheet2,
excluding weekends.

Please Login or Register  to view this content.

[Nitzan]

That looks great but I would like to do that without a Userform, is that possible?

I tried to take part of your code and I get error 424 on the line
ComboBox1.List = yearDict.Keys
altough I did create the ComboBox1.
I removed the Me. cause it's not a userform but I don't think this is the problem....

any ideas?

Please Login or Register  to view this content.

[xLJer]

Sorry, I can't advise you on this. I am not familiar
with using combo boxes on a worksheet, and since you have
a newer version of Excel, maybe someone on the forum can
advise you whether you can do what my form does, but on
the worksheet with Excel 2010.

[Nitzan]

Still can't figure it out, something here is really wrong because I can't get to make even a simple combobox with changing range.
I tried even simple codes just to see how it works and all failed!

some of them contains this:
Me.ComboBox1.List
or
Me.ComboBox1.ListFillRange
and I get the error "Invalid use of Me Keyword"

some of them are basic:
ComboBox1.List
or
ComboBox1.ListFillRange
and I get the error "Object required" (although I did create the combobox)

Please pay attention that I'm trying to populate a combobox from sheet and not from a userform.

Anyone PLEASE??

[xLJer]

I believe that you will get help with the "Object Required" error
if you attach a file that gives you this error.

[Nitzan]

can't attach any file, it's a secure network at my place of work.

I'll be very grateful if someone can help without any attachment....

[xLJer]

Try this. In a new sheet, add a combo box from the ActiveX controls.
Click on the combo in design mode and display the properties list to view
what the name of the combo is; it should be ComboBox1.

Leave design mode whenever you want to test populating the combo.

Copy and paste code you have used in your other trials to
populate the combo box, and this code should be run in the module
of the same worksheet that has the combo.

If you get an error and can't work through it, post your VBA code
in hash tags, and maybe that will give someone a clue about the
problem.