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


"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

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*

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*

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

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

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!