Control Source / Row Source very unstable
 

Hi guys,

I've read a lot of posts and they don't agree on how to reference cells in
the Control Source and the Row Source Proprieties of a ListBox or ComboBox.
I've tried the short form ("Sheet1!A1") and the long form
(Workbooks("Book1").Worksheets("Sheet1").Range("A1 ") or even the longer one,
with .Address at the end. NONE OF THESE ARE STABLE. They sometimes work and
sometimes, I get the "Could not set Property Value....."

Isn't there ONE RIGHT way to tell a control where to get its data? If not,
then what are the rules for using one instead of the other?

Thanks for lighting my path,
Marc

Control Source / Row Source very unstable
 

In the case of listbox and combobox, there is more than one way. You can
fill them with the Initialize process in code by using the AddItem method
(See VBA help). A second way, using code is:

With Worksheets(YourSheetNumber)
Set lb = .Shapes.AddFormControl(xlListBox, 100,10,100,100)
lb.ControlFormat.ListFillRange = "A1:A10"
End With

This creates the ListBox, positions it and sizes it while specifying the
range where the data resides that will be displayed in the listbox.

I have yet to find where it explains how to put a header in the box using
code.

"Marc Gendron" wrote:

Hi guys,

I've read a lot of posts and they don't agree on how to reference cells in
the Control Source and the Row Source Proprieties of a ListBox or ComboBox.
I've tried the short form ("Sheet1!A1") and the long form
(Workbooks("Book1").Worksheets("Sheet1").Range("A1 ") or even the longer one,
with .Address at the end. NONE OF THESE ARE STABLE. They sometimes work and
sometimes, I get the "Could not set Property Value....."

Isn't there ONE RIGHT way to tell a control where to get its data? If not,
then what are the rules for using one instead of the other?

Thanks for lighting my path,
Marc