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.

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

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.

"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

give this a try

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

--


Gary


"Strong Eagle" wrote in message
...
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.

"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

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" wrote in message
...
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.

"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

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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.

"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

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

"Strong Eagle" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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.

"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

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.

"Strong Eagle" 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?

Many thanks.