Listbox Simple Question

[Yesuslave]

Hi all,

OK, I have a macro that opens a new worksheet in Excel. That worksheet has a listbox called lstBank. I want to get the value in lstbank. No matter what I try, i can't seem to find out how to do something as simple as put a msgbox up with the highlighted member of the list in it.

msgbox lstbox

only returns a null string.

I can't seem to find the answer anywhere on the net, and i know this should be the easiest thing in the world.

Thanks,
Joshua Wise

[manavo11]

msgbox ltsbox.Text ?

[Yesuslave]

lstbank.text only gives me the error message "Object Required"

I know I have the name right on the control, but I'm not sure how to reference it. I'm using Excel 97.

Thanks,
Joshua

[Yesuslave]

OK, I got my reference to work for lstbox.text, however, now I need to know if there is a way to pull the data that is visible, not just selected. I have a list box that only shows one piece of data at a time. Is there a way to pull the VISIBLE data instead of the SELECTED data?


Thanks,
Joshua Wise

[WorkHorse]

If it only shows one item at a time, the visible item will always be the top item. You can use the TopIndex property to get the index value of this item and then return the text of that index item in the list.

MsgBox lstBank.List(lstBank.TopIndex)

To always select the visible item, on the click event set the ListIndex equal to the TopIndex.