Finding Cell Address from Listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eubanks - 23 Dec 2006 16:07 GMT

Hello,

I need some help in finding a specific cell address for a selection from
a ListBox. For example, if the ListBox.RowSource = D15:D25, and the third
item in the list was selected (ListIndex = 2) the cell address D17 is
returned. I have fumbled around with relative positioning and have
accomplished nothing but confusion.

Any help or direction to look would be greatly appreciated.

Thanks,
Ken

Reply to this Message

Jim Cone - 23 Dec 2006 16:58 GMT

Ken,

ListIndex starts its numbering at zero, so the third item selected
has a ListIndex of 2.
Also, if you set the ListIndex to -1 then nothing will be displayed.
The last item in a ListBox has a ListIndex equal to the ListCount -1.

Generally speaking, Collections (like "Worksheets") start their numbering with 1.
Arrays (like the "List" property) start their numbering with 0.

The help file does cover most of this.
For what its worth, the XL97 help file is much easier to use than those provided with later versions.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Eubanks" <no.direct.reply@nospam.org>
wrote in message
Hello,
I need some help in finding a specific cell address for a selection from
a ListBox. For example, if the ListBox.RowSource = D15:D25, and the third
item in the list was selected (ListIndex = 2) the cell address D17 is
returned. I have fumbled around with relative positioning and have
accomplished nothing but confusion.
Any help or direction to look would be greatly appreciated.
Thanks,
Ken

Reply to this Message

Mike Fogleman - 23 Dec 2006 18:47 GMT

This seems to work for me:

Private Sub ListBox1_Change()
Dim rng As Range
Dim LstAdr As String
Set rng = Range(ListBox1.ListFillRange)
LstAdr = Cells(WorksheetFunction.Index(rng, ListBox1.ListIndex + 1).Row, _
Range(ListBox1.ListFillRange).Column).Address
Range("A1").Value = LstAdr
End Sub

Mike F

> Hello,
>
[quoted text clipped - 8 lines]
> Thanks,
> Ken

Reply to this Message

Eubanks - 23 Dec 2006 19:44 GMT

Mike,

Most Excellent!!! Thanks! I decided to use a ComboBox instead of a
ListBox so I had to modify the code slightly to work with its properties
(RowSource vs ListFillRange).

Thanks again for getting me off high center.
Ken

> This seems to work for me:
>
[quoted text clipped - 20 lines]
>> Thanks,
>> Ken

Reply to this Message

Mike Fogleman - 23 Dec 2006 20:04 GMT

Glad it worked. Thanks for the feedback!
Mike F

> Mike,
>
[quoted text clipped - 30 lines]
>>> Thanks,
>>> Ken

Reply to this Message