How access a listbox on a worksheet

[Guest]

Hello.
I have a listbox allocated on a worksheet, not on a form.
I set input range and linked cell through properties window.

How can i access the listbox from macro? For example, access selected item in it.

Thanks

 

[Ron de Bruin]

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value

 

[Guest]

Thanks for the answer.
But the problem still exists.

I use locale version (russian) and my listbox name isn't ListBox1, but with russian symbols. Besides the name contains spaces.

So, can I get control's reference with other method. May be using enumerations (or collection) of the all controls on the worksheet.

Addition: I can't see this object in dropdown list followed by sheet's name and dot, and can't see it in the dropdown list of objects (left - upper corner).

 

[Dick Kusleika]

AsIs

If the listbox is from the Forms toolbar, it is part of the Shapes
collection

Sheet1.Shapes(1).Value

or the ListBoxes collection

Sheet1.ListBoxes(1).Value

The ListBoxes collection is deprecated (no longer supported), but it still
works.

If the listbox is from the Control Toolbar (Active X), then it's part of the
OLEObjects collection

Sheet1.OLEObjects(1).Value
Sheet1.OLEObjects("ListBox1").Value

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thanks for the answer.
But the problem still exists.

I use locale version (russian) and my listbox name isn't ListBox1, but

with russian symbols. Besides the name contains spaces.

So, can I get control's reference with other method. May be using

enumerations (or collection) of the all controls on the worksheet.

Addition: I can't see this object in dropdown list followed by sheet's

name and dot, and can't see it in the dropdown list of objects (left - upper
corner).

 

[Ron de Bruin]

AFAIK you can't have spaces in the name of a listbox
When you are in the design mode press the properties button
Try to rename the button there and you see it is not allowed to use spaces

 

[Tom Ogilvy]

Sheet1.OLEObjects(1).Value

Sheet1.OLEObjects("ListBox1").Value

probably was meant to be
Sheet1.OLEObjects(1).Object.Value
Sheet1.OLEObjects("ListBox1").Object.Value

 

[Dick Kusleika]

Yep. Thanks.

Dick

probably was meant to be
Sheet1.OLEObjects(1).Object.Value
Sheet1.OLEObjects("ListBox1").Object.Value