Loading a Combo Box with the months of the year
 

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

Loading a Combo Box with the months of the year
 

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


"Eclypse" wrote:

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

Loading a Combo Box with the months of the year
 

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

Thanks again,

Cheers,
- Eric


On Apr 12, 3:22 pm, JMB wrote:
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.

*snip*

Loading a Combo Box with the months of the year
 

Good to hear that worked for you.

"Eclypse" wrote:

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

Thanks again,

Cheers,
- Eric


On Apr 12, 3:22 pm, JMB wrote:
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.

*snip*

Loading a Combo Box with the months of the year
 

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

Eclypse wrote:

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

--

Dave Peterson

Loading a Combo Box with the months of the year
 

Thanks Dave, that's a particularly slick way of doing it.

Cheers for your help!
- Eric


On Apr 13, 12:17 am, Dave Peterson wrote:
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

Loading a Combo Box with the months of the year
 

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!