Loading a Combo Box with the months of the year

  • Thread starter Thread starter Eclypse
  • Start date Start date
E

Eclypse

Hi,

I'm trying to make a form (using Excel VBA) that will offer a drop-
down combo box to choose a month.

The only way I've been able to do this is to have a sheet containing
all the months, and load the list from there. If possible though, I'd
like to avoid having nearly-empty sheets hanging around.

Is there any way to load the months directly into the combo box list
through VBA?

Thanks!
- Eric
 
Perhaps something similar to this could be used. Double click on your
userform and paste the code into the code module. Change "ComboBox1" to
whatever name your combobox is.

Private Sub UserForm_Initialize()
Dim i As Long
Dim varMonths As Variant

varMonths = Array("Jan", "Feb", "Mar", _
"Apr", "May", "Jun", "Jul", "Aug", _
"Sep", "Oct", "Nov", "Dec")

For i = LBound(varMonths) To UBound(varMonths)
Me.ComboBox1.AddItem varMonths(i)
Next i

End Sub
 
That worked a charm - Thanks a lot! I've been looking for the answer
to that for months. :-)

Thanks again,

Cheers,
- Eric


Perhaps something similar to this could be used. Double click on your
userform and paste the code into the code module. Change "ComboBox1" to
whatever name your combobox is.
Click to expand...

*snip*
 
Good to hear that worked for you.

Eclypse said:
That worked a charm - Thanks a lot! I've been looking for the answer
to that for months. :-)

Thanks again,

Cheers,
- Eric




*snip*
Click to expand...
 
A couple more options:

Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 12
Me.ComboBox1.AddItem Format(DateSerial(2007, iCtr, 1), "mmm")
Next iCtr
End Sub


Or if you're using xl2002 or above (IIRC), you could use MonthName:

Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 12
Me.ComboBox1.AddItem MonthName(iCtr, abbreviate:=True)
Next iCtr
End Sub
 
I was just offered a particularly nice suggestion, thought I'd post it
here for the benefit of others:

Me.ComboBox1.List() = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

That's it!

Thanks both of you again for your help.

Cheers!
 
Back
Top