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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=528631
>

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=528631
>

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

Please Login or Register  to view this content.

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)

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=528631
>