Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error 13 when setting worksheet listbox | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Help! Activate worksheet from a listbox | Excel Worksheet Functions | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |