Fill ComboBox with all dates between two ranges.

[DejaVu]

I have a worksheet with a combo box on it. Each time I execute one of
my macros, I need all the values in the combobox to be cleared out,
then filled with all the dates between two ranges. In my sheet, I have
two cells with two dates. In P3 I have 1/1/1998. In P4 I have
2/1/2006. I need the combobox to list all the months in this range. I
would like to have the formatted as 1998 Jan, 1998 Feb, etc...

The entire combo box would have all the dates from Jan 1, 1998 to
(currently) Feb, 1, 2006.

1998 Jan
1998 Feb
...
...
...
2006 Jan
2006 Feb

Any ideas?? I don't have all my dates listed out, so I'm wondering if
that would be a problem?

TIA,
DejaVu

 

[Bob Phillips]

With Combobox1
.Clear
For i = Range("P3").Value To Range("P4").Value
.AddItem Format(i,"dd mmm yyyy")
Next i
End With

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[DejaVu]

Thanks for the response Bob,

I get an error on the *.Clear* line

The only thing I changed in your code was the name of the combobox from
_With_Combobox1_ to _With_cmbDropDownDate_.

Error:
Run-Time error '-2147467259 (80004005)':
Unspecified error.

Thanks,
DejaVu

 

[Bob Phillips]

That shouldn't be a problem. It is a control toolbox or userform combobox I
assume.

Maybe just remove the line. It is only really necessary when re-populating.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[DejaVu]

Bob - Thanks for your help. That is exactly what I was looking for. I
only had one small problem with it; it added each day within that
range. I was able to easily overcome this with:

Code:
--------------------
tmpDate = DateAdd("yyyy", 1, Sheets(1).Range("P3"))
With Sheets(1).cmbBox1
.Clear
Do Until tmpDate > Sheets(1).Range("P4").Value
.AddItem Format(tmpDate, "yyyy mmm")
tmpDate = DateAdd("m", 1, tmpDate)
Loop
End With
--------------------


I have one more question though. If I copy this worksheet to a new
workbook, all the dates in the drop down menu are gone. Is it possible
to have it retain those values?

Thanks again for all your help,

DejaVu

 

[Bob Phillips]

Not really sure what is causing that, but have you adjusting the combo
reference/

tmpDate = DateAdd("yyyy", 1, Sheets(1).Range("P3"))
With Activesheet.cmbBox1
.Clear
Do Until tmpDate > Sheets(1).Range("P4").Value
.AddItem Format(tmpDate, "yyyy mmm")
tmpDate = DateAdd("m", 1, tmpDate)
Loop
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)