Fill combo with Month-Year starting 2 years ago

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Fill combo with Month-Year starting 2 years ago

Post by Rudi »

Hi,

Stuck on this!

Excel VBA
I need to fill a combobox with dates starting with (Date - 24months) to current (Date). the combo date display must be MMMM-YYYY
Is the loop below correct...I just need a counter to increment the months, but how do do the years...etc...

Code: Select all

Dim dtD As Date
    dtD = DateSerial(Year(Date), Month(Date - 2), Day(1))
    Do While dtD < Date
        dtD = dtD + DateSerial(Year(dtD),Month(dtD)+1,Day(1))
        Sheet2.cbo_ReturnMonth.AddItem (Format(dtD, "mmmm-yyyy"))
    Loop
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fill combo with Month-Year starting 2 years ago

Post by HansV »

DateSerial is flexible: if you set the month number to > 12, the date will automatically shift to the next year. So you could use

Code: Select all

    Dim dtD As Date
    Dim i As Long
    For i = 0 To 24
        dtD = DateSerial(Year(Date) - 2, Month(Date) + i, 1)
        Sheet2.cbo_ReturnMonth.AddItem (Format(dtD, "mmmm-yyyy"))
    Next i
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Fill combo with Month-Year starting 2 years ago

Post by Rudi »

Spot on...
Didn't know about that flexibility. TX
:chocciebar:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Fill combo with Month-Year starting 2 years ago

Post by HansV »

You can also set the month to zero or to a negative number to go back. The same holds for the day.
Best wishes,
Hans