How access a listbox on a worksheet
 

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

How access a listbox on a worksheet
 

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message ...
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

How access a listbox on a worksheet
 

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" wrote:

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message ...
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

How access a listbox on a worksheet
 

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" wrote in message
...
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" wrote:

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message
...
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

How access a listbox on a worksheet
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message ...
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" wrote:

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message ...
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

How access a listbox on a worksheet
 

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


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

--
Regards,
Tom Ogilvy


"Dick Kusleika" wrote in message
...
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" wrote in message
...
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" wrote:

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message
...
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

How access a listbox on a worksheet
 

Yep. Thanks.

Dick

Tom Ogilvy wrote:
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" wrote in message
...
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" wrote in message
...
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" wrote:

Hi AsIs

This will give you the selected item

MsgBox Sheets("Sheet1").ListBox1.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AsIs" wrote in message
...
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