Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking an Active X Listbox to a worksheet list
I am working with userforms to modify a List on a worksheet, which now seems
to work fine. When the List has been modified, I wanted its updated version to be reflected in an embedded Active X Listbox on another worksheet. This doesn't happen automatically, although the Listbox is set up with the range of the List as ListfillRange. So I tried re-linking the List to the Listbox explictly using the ControlFormat property of OLEObjects / Shapes. However, I get an error message "Object doesn't support this property or method". I can verify that it is indeed the Listbox my ControlFormat property is referring to. I also set up a watch on the object of the Listbox itself, and can see that the ControlFormat properties are the ones consistent with a listbox. For instance the ListFillRange is on the list of ControlFormats. But on the watch, most of these properties are also marked with "Object doesn't support..." Basically I just need a function that let's my embedded Active X control re-initialise itself from the named range it's already connected to through its ListFillRange property on the Properties list. Any ideas of what's the problem? Any ideas of simpler ways to achieving the functionality I want? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking an Active X Listbox to a worksheet list
not tested but give this a try:
Dim strRowSource As String strRowSource = Sheet1.Name & "!" & Sheet1.Range _ ("A1", Sheet1.Range("A65536").End(xlUp)).Address With Worksheets(1).OLEObjects("ListBox1") .ListFillRange = vbNullString .ListFillRange = strRowSource End With -- jb "OleAtNordic" wrote: I am working with userforms to modify a List on a worksheet, which now seems to work fine. When the List has been modified, I wanted its updated version to be reflected in an embedded Active X Listbox on another worksheet. This doesn't happen automatically, although the Listbox is set up with the range of the List as ListfillRange. So I tried re-linking the List to the Listbox explictly using the ControlFormat property of OLEObjects / Shapes. However, I get an error message "Object doesn't support this property or method". I can verify that it is indeed the Listbox my ControlFormat property is referring to. I also set up a watch on the object of the Listbox itself, and can see that the ControlFormat properties are the ones consistent with a listbox. For instance the ListFillRange is on the list of ControlFormats. But on the watch, most of these properties are also marked with "Object doesn't support..." Basically I just need a function that let's my embedded Active X control re-initialise itself from the named range it's already connected to through its ListFillRange property on the Properties list. Any ideas of what's the problem? Any ideas of simpler ways to achieving the functionality I want? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking an Active X Listbox to a worksheet list
thanks, John,
I'll have a look at this, it may give some other clues. but actually figured out what I did wrong in the meantime. Problem seems to be that the ControlFormat property only applies to Control objects, not Active X. The Active X control can be referenced very simply: Sheet1.ListBox1.ListFillRange = Range("r_WF_dataForListBox").Name ..ole "john" wrote: not tested but give this a try: Dim strRowSource As String strRowSource = Sheet1.Name & "!" & Sheet1.Range _ ("A1", Sheet1.Range("A65536").End(xlUp)).Address With Worksheets(1).OLEObjects("ListBox1") .ListFillRange = vbNullString .ListFillRange = strRowSource End With -- jb "OleAtNordic" wrote: I am working with userforms to modify a List on a worksheet, which now seems to work fine. When the List has been modified, I wanted its updated version to be reflected in an embedded Active X Listbox on another worksheet. This doesn't happen automatically, although the Listbox is set up with the range of the List as ListfillRange. So I tried re-linking the List to the Listbox explictly using the ControlFormat property of OLEObjects / Shapes. However, I get an error message "Object doesn't support this property or method". I can verify that it is indeed the Listbox my ControlFormat property is referring to. I also set up a watch on the object of the Listbox itself, and can see that the ControlFormat properties are the ones consistent with a listbox. For instance the ListFillRange is on the list of ControlFormats. But on the watch, most of these properties are also marked with "Object doesn't support..." Basically I just need a function that let's my embedded Active X control re-initialise itself from the named range it's already connected to through its ListFillRange property on the Properties list. Any ideas of what's the problem? Any ideas of simpler ways to achieving the functionality I want? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ListBox RowSource set to range of in-active sheet - possible? | Excel Programming | |||
bind data to active x listbox without macro | Excel Discussion (Misc queries) | |||
Userform Listbox using an active sheet | Excel Discussion (Misc queries) | |||
Multiselect ListBox - Active/Inactive Items | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |