finding line number selected in combo box

[Bonsai Bill]

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.

 

[Rick Rothstein]

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 Rothstein]

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

 

[Bonsai Bill]

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]

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.

 

[Bonsai Bill]

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!