View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Initializing Multiple Combo Boxes

I am about to create a form which utilizes about 15 identical combo boxes
(each is used to control the curency to be used for a calculation, and
each
is identical to the other).

Right now I have a form with two currency combo boxes and I separately
initialize the values in each box (about 30 rows for 30 currencies). I
just
cannot see doing this for 15 boxes, not so much the first time set up but
the
maintenance hassles if I add or delete currencies... I don't want to do it
15
times.

So, is there a way to initialize all 15 combo boxes at one time from a
list
or some such?


Assuming these ComboBox'es are the only ComboBox'es on the UserForm, this
code will do that...

Dim X As Long
Dim Items() As String
Dim oCtrl As Object
Items = Split("One|Two|Three|Four|Five", "|")
For Each oCtrl In Controls
If TypeOf oCtrl Is ComboBox Then
For X = 0 To UBound(Items)
oCtrl.AddItem Items(X)
Next
End If
Next

Just put your list inside the Split function, separating each item with a
delimiter character that will never appear inside the text of your items.
For this example, I used the "pipe symbol" (the vertical bar).

Rick