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

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy

"sailingdan " wrote in message
...
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 from
a SpinButton, so I don't know in advance how many ComboxBoxes are
created until I change the SpinButton.

When creating the ComboBoxes, I get to name them ("ComboBox" & i) as I
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 a
ComboBox when part of the name of the ComboxBox is a variable?

Thanks!


---
Message posted from http://www.ExcelForum.com/

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.Combo Box.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

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy



"sailingdan " wrote in message
...
Tom,

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

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

I use a SpinButton to create a new ComboBox when I increment the number
of authors SpinButton. I delete the last ComboBox when I decrement the
SpinButton. So, it's important to have the flexibilty to add a unique
and predictably named ComboBox, and populate it with the author list,
so I can create and destroy the ComboBoxes in an orderly fashion. Look
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.Combo Box.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 figure
out what. If you have a more elegant solution, or see what I'm doing
wrong, let me know. Otherwise I'll need to change my plan of attack.

Thanks.


---
Message posted from http://www.ExcelForum.com/