How access a listbox on a worksheet

A

AsIs

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
 
R

Ron de Bruin

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value
 
A

AsIs

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

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

AsIs said:
Thanks for the answer.
But the problem still exists.

I use locale version (russian) and my listbox name isn't ListBox1, but
Click to expand...
with russian symbols. Besides the name contains spaces.
So, can I get control's reference with other method. May be using
Click to expand...
enumerations (or collection) of the all controls on the worksheet.
Addition: I can't see this object in dropdown list followed by sheet's
Click to expand...
name and dot, and can't see it in the dropdown list of objects (left - upper
corner).
 
R

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
 
T

Tom Ogilvy

Sheet1.OLEObjects(1).Value
Sheet1.OLEObjects("ListBox1").Value
Click to expand...

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