Initializing Multiple Combo Boxes

[Strong Eagle]

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?

Many thanks.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Strong Eagle]

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
sheet 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 sheet 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 sheet.

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

Thanks.

 

[Gary Keramidas]

give this a try

Sheets("Estimator").ComboBox1.ListFillRange = "ExchangeRates!B3:B23"

--


Gary

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
sheet 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 sheet 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 sheet.

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

Thanks.

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

 

[Rick Rothstein \(MVP - VB\)]

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

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
sheet 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 sheet
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
sheet.

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

Thanks.

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

 

[Strong Eagle]

Interesting. So, the control properties use cell notation as in a cell, as
opposed to the methods syntax for VB for Excel. I waste more time on syntax.
Thanks for the answers.

And one off the top. If I were writing this in C, I could specify one array
and point multiple list boxes to it, saving the item in the control itself.
Is that possible to to with VB for excel if I have 15 identical lists?

Yes, I need all the lists. For example, pay might be in Sing $$, housing
expense in Thai Baht, and travel expense in Kenya Shillings. Need to bring
all to a common currency to see how I make money.

give this a try

Sheets("Estimator").ComboBox1.ListFillRange = "ExchangeRates!B3:B23"

--


Gary

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
sheet 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 sheet 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 sheet.

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

Thanks.

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

 

[Strong Eagle]

Yes, Rick, I have the combo boxes on a user form... rather nifty, I would
say. Then, I have a second sheet, currently 40 rows of 40 countries with
columns for country, currency symbol, exchange rate (mid market, and
buy/sell).

I figure I will load just the country name into the combo box, then use a
cell reference to extract symbol for formatting purposes and exchange rate
for computation purposes.

I can now load the combo boxes from the second sheet using ListFillRange and
it looks like the computations will be mostly easy.

Thanks for your help.

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

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
sheet 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 sheet
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
sheet.

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

Thanks.

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

 

[NickHK]

Not sure if you are talking about a userform or not now, but after you have
one Listbox filled you can

ListBox2.List = ListBox1.List

although using .ListFillRange/.RowSource for a single source of data is
probably better.

NickHK

Yes, Rick, I have the combo boxes on a user form... rather nifty, I would
say. Then, I have a second sheet, currently 40 rows of 40 countries with
columns for country, currency symbol, exchange rate (mid market, and
buy/sell).

I figure I will load just the country name into the combo box, then use a
cell reference to extract symbol for formatting purposes and exchange rate
for computation purposes.

I can now load the combo boxes from the second sheet using ListFillRange and
it looks like the computations will be mostly easy.

Thanks for your help.

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

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
sheet 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 sheet
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
sheet.

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

Thanks.

:

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

 

[Strong Eagle]

Thanks very much to all. I now have a very nifty Cost and Revenue Estimating
Worksheet that allows me to price project managers all over Asia and Europe.

Should anyone want a copy, please drop a note.