Multiple ComboBox Lists

[sailingdan]

I'm new to using user forms and I'm having some trouble.

I am creating multiple ComboBoxes with a loop. The loop count is fro
a SpinButton, so I don't know in advance how many ComboxBoxes ar
created until I change the SpinButton.

When creating the ComboBoxes, I get to name them ("ComboBox" & i) as
create them. However, I can't figure out how to get list data to them
I tried:

Controls("ComboBox" & i).list = MyListArray

but this doesn't seem to work. Is there a way to assign list data to
ComboBox when part of the name of the ComboxBox is a variable?

Thanks

 

[Tom Ogilvy]

if the name of the combobox is in fact Comboboxi for whatever the value of
i is, the command you show should work. As I recall, I have occasionally run
into problems trying to work with the properties of a control in the same
procedure that created the control. You might create the controls in design
mode and make them hidden, then make the appropriate number visible.

 

[sailingdan]

Tom,

Thanks for the reply. However, this isn't music to my ears. If you'r
game, perhaps giving you bigger piece of the problem will help.

I'm trying to create a database for my papers I've collected. Som
papers have multiple authors. I want to create a ComboBox for eac
Author, for the number of Authors I have. I have a list of Authors o
another sheet that I want to select from.

I use a SpinButton to create a new ComboBox when I increment the numbe
of authors SpinButton. I delete the last ComboBox when I decrement th
SpinButton. So, it's important to have the flexibilty to add a uniqu
and predictably named ComboBox, and populate it with the author list
so I can create and destroy the ComboBoxes in an orderly fashion. Loo
at what I have in the SpinUp method for the SpinButton.

-------
Private Sub SpinButton1_SpinUp()

SpinIndex = SpinButton1.Value

'... AuthorCount is 4, deturmined from another procedure

'populate the Author List. This works.
ReDim AuthorArray(AuthorCount - 1) As String
For i = 0 To AuthorCount - 1
AuthorArray(i) = Sheets("Lists").Cells(4 + i, 4)
Next i

'Add the ComboBox
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=150
Top:=107 + 25.5 * SpinIndex, Width:=116.25, Height:=18.75).Select

'Name the new ComboBox. This works.
Selection.Name = "ComboBox" & (SpinIndex)

'Now I want to populate it. But I get "Sub or Function not defined"
Controls("ComboBox" & (SpinIndex)).List = AuthorArray

End Sub
----

I think I must be missing some level of hierarchy, but I can't figur
out what. If you have a more elegant solution, or see what I'm doin
wrong, let me know. Otherwise I'll need to change my plan of attack.

Thanks

 

[Tom Ogilvy]

Based on the single line of code you originally showed, using the controls
collection, I assumed these were on a useform.

You now show it is on a worksheet. There is no controls collection on a
worksheet. The comboboxes are in the oleobjects and shapes collection.

'Now I want to populate it. But I get "Sub or Function not defined"
Controls("ComboBox" & (SpinIndex)).List = AuthorArray

should probably be
me.oleObjects(Combobox" & spinIndex).List = AuthorArray

although you may still have problems for the original reason I gave;
hopefully not.