Multiple ComboBox Lists
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 |
Multiple ComboBox Lists
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/ |
Multiple ComboBox Lists
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 |
Multiple ComboBox Lists
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/ |
All times are GMT +1. The time now is 03:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com