Parent Of A Range Object

[Greg M]

Hi all, and a very happy new year to everyone!

The Parent property of a Range object should always return a Worksheet object, right?

Well that's what I always thought until I received a "Type Mismatch" error when running the following code:

Please Login or Register  to view this content.

It appears that the Parent object of a Range object returned as the TopLeftCell property of a Shape object, is not the Worksheet object, but the SHAPE object. This can be seen from the following code:

Please Login or Register  to view this content.

I suppose that the above is not really illogical, but it probably is unexpected.

Hope this might help someone from the head-banging I endured while trying to sort out the above. (Oh yes, it's very simple when it's all sorted out, but not so simple beforehand! )

Regards,

Greg M

[Sintek]

always return a Worksheet object, right

Not necessarily...All depends on the Parent-Child correlation...Controls live inside other controls...
As I understand...Parent will reference the control preceding as you have alluded to above...
Not all controls have children though...
Then again...I might be wrong...Perhaps they aren't married and choose not to have kids lol...

[Haluk]

Isn't it just;

Please Login or Register  to view this content.

[Sintek]

Simply...

Please Login or Register  to view this content.

[Greg M]

Hi Sintek & Haluk,

Many thanks for your responses, but errrmmm . . . didn't I already say:

Please Login or Register  to view this content.

Meanwhile, the plot thickens!

The attached workbook contains the following code:

Please Login or Register  to view this content.

The worksheet contains a Rounded Rectangle and an MSForms CommandButton, to each of which is assigned the macro "ShowParentName". Each of these is recognised as a Shape object.

Clicking on the Rounded Rectangle displays the name of the Rounded Rectangle, and then displays the name of the Worksheet.

Clicking on the MSForms CommandButton displays the name of the Worksheet, and then displays the name of the Workbook.


So, apparently the Parent property of a Range object returned by the TopLeftCell property of a Shape object:

is a Shape object when that Shape object is a Rounded Rectangle, and

is a Range object when that Shape object is an MSForms CommandButton.

Curiouser and curiouser

Regards,

Greg M

[romperstomper]

If you specifically want the worksheet, use the Range's .Worksheet property.

Also, that is not an MSForms.CommandButton. It's a native Excel Form button.

[Greg M]

Hi romperstomper,

Many thanks for your reply.

Ok on using the Worksheet property, but the post was more about pointing out the differences associated with the type of Shape object involved, rather than finding something which would return the correct object.

Also, that is not an MSForms.CommandButton. It's a native Excel Form button

That indeed is a distinction of which I was unaware until now - chaque jour on apprend quelque chose!

Thanks again,

Greg M

[Sintek]

Just once again...One of Excel's "Ghost in the Machine Issues"
I personally always assumed it was the preceding link for all objects...
But like I said...

Perhaps they aren't married and choose not to have kids lol...

Suggestion...Work around the issue...Like you have done...
One day...one problem at a time...