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:


Sub CodeWithError()

    Const sSHAPE_NAME   As String = "shpTest"

    Dim rShapeCell      As Range
    Dim wksActive       As Worksheet
    Dim wksParent       As Worksheet
    Dim shpTest         As Shape

    Set wksActive = ActiveSheet

    Set shpTest = wksActive.Shapes(sSHAPE_NAME)

    Set rShapeCell = shpTest.TopLeftCell

    Set wksParent = rShapeCell.Parent               '   <<<     "Type Mismatch" error

End Sub

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:


Sub ShowParentName()

    Const sSHAPE_NAME   As String = "shpTest"

    Dim rShapeCell      As Range
    Dim wksActive       As Worksheet
    Dim shpTest         As Shape

    Set wksActive = ActiveSheet

    Set shpTest = wksActive.Shapes(sSHAPE_NAME)

    Set rShapeCell = shpTest.TopLeftCell

    MsgBox rShapeCell.Parent.Name                   '   <<<     Returns "shpTest"

    MsgBox rShapeCell.Parent.Parent.Name            '   <<<     Returns "Sheet1"

    MsgBox Range(rShapeCell.Address).Parent.Name    '   <<<     Returns "Sheet1"

End Sub

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