Can you help understand why this code works:


Code:
--------------------
Sheets(1).Shapes("ComboBoxA").Select
Selection.ListFillRange = "lista2"
--------------------



and this one don't:



Code:
--------------------
Sheets(1).Shapes("ComboBoxA").ListFillRange = "lista2"
--------------------



Thank you all


Joe


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381040

Hi Jose,

jose luis wrote:
Can you help understand why this code works:
--------------------
Sheets(1).Shapes("ComboBoxA").Select
Selection.ListFillRange = "lista2"
--------------------

and this one don't:
--------------------
Sheets(1).Shapes("ComboBoxA").ListFillRange = "lista2"
--------------------

Sheets(1).Shapes("ComboBoxA") is an object of type "Shape". Thus, there is
no ListFillRange property available. When you first Select the object, then
Selection refers to the actual "DropDown" object, which does have a
ListFillRange property available. So that's why selecting it first works.

Another option is to use the OLEFormat and Object properties to get a
reference to the actual object type:

Sheets(1).Shapes("ComboBoxA").OLEFormat.Object.Lis tFillRange = "lista2"

Or you can use the DropDowns collection:

Sheets(1).DropDowns("ComboBoxA").ListFillRange = "lista2"

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

But this works:

Sheets(1).Shapes("ComboBoxA").DrawingObject.ListFi llRange = "lista2"

Shapes don't have a direct .listfillrange property. But lots of properties are
buried under other properties.

If you select
Sheets(1).Shapes("ComboBoxA")
and then add a watch (shift-F9)

You can see all those properties that that shape has.

You could also go right to the combobox itself:
Sheets(1).ComboBoxA.ListFillRange = "list2a"

Working with the selection hides lots of these properties--but then your code
has to select stuff.

jose luis wrote:

Can you help understand why this code works:

Code:
--------------------
Sheets(1).Shapes("ComboBoxA").Select
Selection.ListFillRange = "lista2"
--------------------

and this one don't:

Code:
--------------------
Sheets(1).Shapes("ComboBoxA").ListFillRange = "lista2"
--------------------

Thank you all

Joe

--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381040

--

Dave Peterson

Thank you so much Jake and Dave for your prompt, complete and
understandable response (for novices like myself). I learned the
difference, and solve my programming problem. Hope to read you around
and be able in the near future to help others as you do today.

Thanks again

Jose Luis



jose luis Wrote:
Can you help understand why this code works:


Code:
--------------------
Sheets(1).Shapes("ComboBoxA").Select
Selection.ListFillRange = "lista2"
--------------------



and this one don't:



Code:
--------------------
Sheets(1).Shapes("ComboBoxA").ListFillRange = "lista2"
--------------------



Thank you all


Joe


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381040

One of the differences between parts of our suggestions is that I used a
combobox from the control toolbox toolbar. Jake used a dropdown/combobox from
the Forms toolbar.



jose luis wrote:

Thank you so much Jake and Dave for your prompt, complete and
understandable response (for novices like myself). I learned the
difference, and solve my programming problem. Hope to read you around
and be able in the near future to help others as you do today.

Thanks again

Jose Luis

jose luis Wrote:
Can you help understand why this code works:


Code:
--------------------
Sheets(1).Shapes("ComboBoxA").Select
Selection.ListFillRange = "lista2"
--------------------



and this one don't:



Code:
--------------------
Sheets(1).Shapes("ComboBoxA").ListFillRange = "lista2"
--------------------



Thank you all


Joe

--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381040

--

Dave Peterson

Dave Peterson wrote:
One of the differences between parts of our suggestions is that I
used a combobox from the control toolbox toolbar. Jake used a
dropdown/combobox from the Forms toolbar.

And I always guess wrong, so he probably wanted information on the ActiveX
version. <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

If we both usually guess wrong, do we get stuck with Rod Serling!

Jake Marx wrote:

Dave Peterson wrote:
One of the differences between parts of our suggestions is that I
used a combobox from the control toolbox toolbar. Jake used a
dropdown/combobox from the Forms toolbar.

And I always guess wrong, so he probably wanted information on the ActiveX
version. <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

--

Dave Peterson