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?

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?

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?