Excel combo box - value list?

[Guest]

I created a form through VBA in Excel, and added a combo box to it. Much to
my dismay, and unlike MS Access, it seems like the only way to populate the
rowsource property is to bind it to a column on a spreadsheet.

In MS Access, you can select rowsourcetype = value list, and then hand-type
the values you'd like to show up in the combo box drop-down in the rowsource
property.

I really don't want to have to add another whole worksheet to my workbook to
accomodate a handful of options for a combo box. plus, I seem to be running
into issues if the worksheet with the applicable values is not the active
worksheet when the form is displayed. being able to hand-type the values like
you can in MS Access would be so much nicer.

Is there a way to do this in Excel? Am I missing something?

Thanks, Joel

 

[Patrick Molloy]

you can populate from a range, or use the .ADD method

 

[Tom Ogilvy]

You can either use add item to assign the values in code or you can link
(bind) to the worksheet cells using rowsource.

You can do

userform1.Combobox1.List = Array(1,"Bob",3,4,5,6,"Sally")

Such code could be placed in the initialize event.

To the best of my knowledge, there is no provision to type in values.

 

[Harald Staff]

Hi Joel

You don't have to data bind it. If you have few values (<20 or so) then code
is easier:

Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "Beer"
.AddItem "Wine"
.AddItem "Bourbon"
End With
End Sub

With longer lists I find a bound worksheet easier to maintain.

HTH. Best wishes Harald

 

[Guest]

Harald,
Thanks, this helps.

And, unless they add more months to the year, this combo box list will
pretty much always contain 12 values. : )

Joel

 

[Guest]

Thanks, this helps.

What's the difference between putting it in the Initialize event vs. the
Activate event?

 

[Harald Staff]

Months ? Why didn't you say so ? This is all it takes:

Private Sub UserForm_Initialize()
Dim L As Long
With ComboBox1
For L = 1 To 12
.AddItem Format$(DateSerial(1, L, 1), "mmmm")
Next
End With
End Sub

HTH. Best wishes Harald

 

[Tom Ogilvy]

The initialize event is fired once. the activate event could be fired more
than once. Other than that, I would think it is a matter of preference.

 

[Guest]

I noticed that in vba 6.3, version 9972 the additem method doesn't compile,
but it does in verison 9969. Any body seen that??

 

[Tom Ogilvy]

Possibly you have an invalid reference in the machine where you say it
doesn't compile. After the error, in the VBE hit reset, then go into
tools=>References and see if you have a reference marked as MISSING. If so,
fix it.