Re: Initializing Multiple Combo Boxes

You no longer have your ComboBox'es on a UserForm? Do you still have multiple ComboBox'es that you want to fill with the same data?

Rick




"Strong Eagle" <StrongEagle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:04E1759A-2FDA-4989-9A91-F1149AD5FC19@xxxxxxxxxxxxxxxx
Rick,

Thanks for the pointer. After trying some sample code I figured it would be
even better if I could keep all my currency conversion values in a second
*** and then use

Sheets("Estimator").ComboBox1.ListFillRange = Sheets("Exchange
Rates").["B3:B23"]

at workbook open. The problem is that if the range is on the same *** as
the combobox this works OK but the above statement does not work and the
combobox properties for list range shows only a range of cells, not the ***.

Can I use list fill in this manner or another alternative to load combobox
lines from a spread***.

Thanks.

"Rick Rothstein (MVP - VB)" wrote:

>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





.