I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six simple
two or three word descriptors. Based upon selection in first combo box I
"load" the second combo box which has longer text in items. Macros will be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most likely
response via values. I can determine which value is selected in the boxes
using a string comparison which is a bit messy. Is there a variable that can
tell me which line number in the combo box was selected? That would a much
cleaner way.

One other trivial question: Rowsource requires a string and I see how to to
provide information using sheet numbers. What is syntax for using sheet names
instead?

Thanks in advance for your help! You folks provide a valuable service to us
with less experience.

The ListIndex property of ListBox (same for a ComboBox by the way) will tell
you which item number is selected; but note the index it zero based, so the
first item in the list is 0, the second item is 1, etc. Also, if the
ListIndex is equal to -1 (minus one), then no item is selected. You can get
the text of a particular item in the list by using the List property; so,
the text for the fifth item in a list is ListBox1.List(4)... this can be
handy in a For..Next loop. Also, you can get the text for the select item
using ListBox1.List(ListBox1.ListIndex).

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six
simple
two or three word descriptors. Based upon selection in first combo box I
"load" the second combo box which has longer text in items. Macros will be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most likely
response via values. I can determine which value is selected in the boxes
using a string comparison which is a bit messy. Is there a variable that
can
tell me which line number in the combo box was selected? That would a much
cleaner way.

One other trivial question: Rowsource requires a string and I see how to
to
provide information using sheet numbers. What is syntax for using sheet
names
instead?

Thanks in advance for your help! You folks provide a valuable service to
us
with less experience.

I'm not sure why I thought you asked about a ListBox, but every thing I said
applies to a ComboBox as well... they use the same addressing scheme (mainly
because underneath it all, a ComboBox is really a ListBox combined with a
TextBox).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The ListIndex property of ListBox (same for a ComboBox by the way) will
tell you which item number is selected; but note the index it zero based,
so the first item in the list is 0, the second item is 1, etc. Also, if
the ListIndex is equal to -1 (minus one), then no item is selected. You
can get the text of a particular item in the list by using the List
property; so, the text for the fifth item in a list is ListBox1.List(4)...
this can be handy in a For..Next loop. Also, you can get the text for the
select item using ListBox1.List(ListBox1.ListIndex).

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six
simple
two or three word descriptors. Based upon selection in first combo box I
"load" the second combo box which has longer text in items. Macros will
be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most likely
response via values. I can determine which value is selected in the boxes
using a string comparison which is a bit messy. Is there a variable that
can
tell me which line number in the combo box was selected? That would a
much
cleaner way.

One other trivial question: Rowsource requires a string and I see how to
to
provide information using sheet numbers. What is syntax for using sheet
names
instead?

Thanks in advance for your help! You folks provide a valuable service to
us
with less experience.

Thanks Rick. It works well and eliminates some potentially dodgy code.

Can you offer comparable help on:
One other trivial question: Rowsource requires a string and I see how to to
provide information using sheet numbers. What is syntax for using sheet names
instead? I can always activate a sheet to use the Rowsource but there must
be an easy way to put in the name-a way that I have not guessed yet.

Thanks for your quick and right-on response!

"Rick Rothstein" wrote:

I'm not sure why I thought you asked about a ListBox, but every thing I said
applies to a ComboBox as well... they use the same addressing scheme (mainly
because underneath it all, a ComboBox is really a ListBox combined with a
TextBox).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The ListIndex property of ListBox (same for a ComboBox by the way) will
tell you which item number is selected; but note the index it zero based,
so the first item in the list is 0, the second item is 1, etc. Also, if
the ListIndex is equal to -1 (minus one), then no item is selected. You
can get the text of a particular item in the list by using the List
property; so, the text for the fifth item in a list is ListBox1.List(4)...
this can be handy in a For..Next loop. Also, you can get the text for the
select item using ListBox1.List(ListBox1.ListIndex).

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six
simple
two or three word descriptors. Based upon selection in first combo box I
"load" the second combo box which has longer text in items. Macros will
be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most likely
response via values. I can determine which value is selected in the boxes
using a string comparison which is a bit messy. Is there a variable that
can
tell me which line number in the combo box was selected? That would a
much
cleaner way.

One other trivial question: Rowsource requires a string and I see how to
to
provide information using sheet numbers. What is syntax for using sheet
names
instead?

Thanks in advance for your help! You folks provide a valuable service to
us
with less experience.

I think you want something like this...

ComboBox1.RowSource = Worksheets("Sheet4").Range("A1:A4").Address

where you would use your worksheet's actual name and change the range
address to match your actual row source. You can also do it directly like
you would in the Properties window itself...

ComboBox1.RowSource = "Sheet4!A1:A4"

It kind of depends on whether the row source can be variable (my first
example) or fix (my second example) although, of course, you can dynamically
construct the String value in the second example if you want.

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
Thanks Rick. It works well and eliminates some potentially dodgy code.

Can you offer comparable help on:
One other trivial question: Rowsource requires a string and I see how to
to
provide information using sheet numbers. What is syntax for using sheet
names
instead? I can always activate a sheet to use the Rowsource but there must
be an easy way to put in the name-a way that I have not guessed yet.

Thanks for your quick and right-on response!

"Rick Rothstein" wrote:

I'm not sure why I thought you asked about a ListBox, but every thing I
said
applies to a ComboBox as well... they use the same addressing scheme
(mainly
because underneath it all, a ComboBox is really a ListBox combined with a
TextBox).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The ListIndex property of ListBox (same for a ComboBox by the way) will
tell you which item number is selected; but note the index it zero
based,
so the first item in the list is 0, the second item is 1, etc. Also, if
the ListIndex is equal to -1 (minus one), then no item is selected. You
can get the text of a particular item in the list by using the List
property; so, the text for the fifth item in a list is
ListBox1.List(4)...
this can be handy in a For..Next loop. Also, you can get the text for
the
select item using ListBox1.List(ListBox1.ListIndex).

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six
simple
two or three word descriptors. Based upon selection in first combo box
I
"load" the second combo box which has longer text in items. Macros
will
be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most
likely
response via values. I can determine which value is selected in the
boxes
using a string comparison which is a bit messy. Is there a variable
that
can
tell me which line number in the combo box was selected? That would a
much
cleaner way.

One other trivial question: Rowsource requires a string and I see how
to
to
provide information using sheet numbers. What is syntax for using
sheet
names
instead?

Thanks in advance for your help! You folks provide a valuable service
to
us
with less experience.

Hi Rick,
Your first response is exactly what I need as the ranges are dynamic.

I appreciate your help. I am glad to have some new tricks to use!


"Rick Rothstein" wrote:

I think you want something like this...

ComboBox1.RowSource = Worksheets("Sheet4").Range("A1:A4").Address

where you would use your worksheet's actual name and change the range
address to match your actual row source. You can also do it directly like
you would in the Properties window itself...

ComboBox1.RowSource = "Sheet4!A1:A4"

It kind of depends on whether the row source can be variable (my first
example) or fix (my second example) although, of course, you can dynamically
construct the String value in the second example if you want.

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
Thanks Rick. It works well and eliminates some potentially dodgy code.

Can you offer comparable help on:
One other trivial question: Rowsource requires a string and I see how to
to
provide information using sheet numbers. What is syntax for using sheet
names
instead? I can always activate a sheet to use the Rowsource but there must
be an easy way to put in the name-a way that I have not guessed yet.

Thanks for your quick and right-on response!

"Rick Rothstein" wrote:

I'm not sure why I thought you asked about a ListBox, but every thing I
said
applies to a ComboBox as well... they use the same addressing scheme
(mainly
because underneath it all, a ComboBox is really a ListBox combined with a
TextBox).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The ListIndex property of ListBox (same for a ComboBox by the way) will
tell you which item number is selected; but note the index it zero
based,
so the first item in the list is 0, the second item is 1, etc. Also, if
the ListIndex is equal to -1 (minus one), then no item is selected. You
can get the text of a particular item in the list by using the List
property; so, the text for the fifth item in a list is
ListBox1.List(4)...
this can be handy in a For..Next loop. Also, you can get the text for
the
select item using ListBox1.List(ListBox1.ListIndex).

--
Rick (MVP - Excel)


"Bonsai Bill" wrote in message
...
I am trying a combo box for the first time so my question is hopefully
trivial. I am using two combo boxes. The first is simple and has six
simple
two or three word descriptors. Based upon selection in first combo box
I
"load" the second combo box which has longer text in items. Macros
will
be
used in Excel 2007.

I am using rowsource to load the lists and I also put in the most
likely
response via values. I can determine which value is selected in the
boxes
using a string comparison which is a bit messy. Is there a variable
that
can
tell me which line number in the combo box was selected? That would a
much
cleaner way.

One other trivial question: Rowsource requires a string and I see how
to
to
provide information using sheet numbers. What is syntax for using
sheet
names
instead?

Thanks in advance for your help! You folks provide a valuable service
to
us
with less experience.