Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Address from Listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Address from Listbox
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. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Eubanks" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Address from Listbox
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 "Eubanks" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Address from Listbox
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 "Mike Fogleman" wrote in message m... 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 "Eubanks" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Address from Listbox
Glad it worked. Thanks for the feedback!
Mike F "Eubanks" wrote in message ... 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 "Mike Fogleman" wrote in message m... 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 "Eubanks" 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Cell address by value | Excel Discussion (Misc queries) | |||
Finding Cell address by value | Excel Discussion (Misc queries) | |||
Finding the Address of a Cell | Excel Worksheet Functions | |||
finding the address of a specific cell? | Excel Programming | |||
Finding cell address... | Excel Programming |