Populating multiple combo boxes

[mattboo]

Hello All!

Suppose I have a form which, among other controls, has ten different combo boxes. I wish to populate each of these combo boxes with the same 15 items. Is there anyway to do this without having to write 150 lines of code? A loop of some sort would be preferable. Can anyone help?

Cheers,
Matt

 

[xlbo]

Starter for 10:

Dim ctrl As Control

For Each ctrl In Me.Controls

If TypeName(ctrl) = "ComboBoxx" Then
'Code for adding the items
Else
End If
Next ctrl

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[mattboo]

Geoff,

Cheers! That was my first thought too. Unfortunately I have another 10 different comboboxes on the same form which need different items adding. I've tried creating a collection with intentions of looping over that but adding an item adds the current value (text) of the control instead of the control instead. Perhaps using
Dictionary would work but, no dictionary object available!

I've plumped for this but if anyone else knows a more elegant solution then please let me know:

I've placed the items to be added as a list in column one on a sheet in the same workbook

The following code is added before the form is shown

Dim i As Integer
For i = 1 To 10
.cboBrand1.AddItem Worksheets(3).Cells(i, 1)
Next i

Makes it a bit neater but a nested loop would be heaven!

 

[xlbo]

If you can enclose the seperate sets of combos into 2 different frames, you should be able to loop thru each set within the frame...

For Each ctrl In Me.Frame1.Controls
If TypeName(ctrl) = "ComboBox" Then
'Code for adding the items
Else
End If
Next

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[mattboo]

Geoff,

Nice one! I'l give it a go and let you know how I get on.

Thanks for your help!

Matt

 

[mattboo]

Geoff,

It works!

But.....because the combo boxes are now in frames I'm finding it difficult to set the tab properties. There are a lot of controls on the form and everytime I try to change them VB changes them to whatever it wants. Any ideas?

 

[xlbo]

The tabindex is what you need to set - this will be WITHIN the frame (and starts at 0) and make sure that Tabstop is set to TRUE

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244