CODEWITHSUNDEEP
vbaexcelpropertiesshapes
kwiqry
kwiqry

Reputation: 96

Excel VBA TopLeftCell property

I want to get .TopLeftCell property of each Shape Object in Selection.ShapeRange, but I got runtime error 438 saying 'this object does not support this property or method' when I run following code.

Sub testTopLeftCell()
    For Each target In Selection.ShapeRange
        MsgBox target.TopLeftCell.Address
    Next
End Sub

However, following code works.

Sub testTopLeftCell2()
    For Each target In Selection.ShapeRange
        MsgBox ActiveSheet.Shapes(target.Name).TopLeftCell.Address
    Next
End Sub

Could anyone teach me, what are the problems in the first code and why the second code works. I got really confused with this problem.

Upvotes: 0

Views: 11006

Answers (2)

kwiqry
kwiqry

Reputation: 96

For Each for ShapeRange Collection does not offer the access to ShapeRange(i), so target in the first code is an instance of ShapeRange object(reffering same Object reffered by ShapeRange(i), but is not a Shape object.)

According to reference articles on MSDN, ShapeRange Object does not have the .TopLeftCell property.

On the other hand, ShapeRange(i) means ShapeRange.Item(i) which returns single Shape Object with .TopLeftCell property. Of course, we also can access Shape Object as a member of Shapes Collection by Shapes(j).

So, we can think the ShapeRange Object as a kind of interfaces for the Shapes Object, which offers properties slightly different from those Shape Object has(in this case ShapeRange does not have .TopLeftCell property but Shape has.)

In other cases ShapeRange offers same properties as Shape Object does.

This is why confusions come up with following code which returns same result 3 times, when single Shape Object is selected. But these result comes up, simply because .Name property and TypeName Function returns same result for ShapeRange Object and Shape Object.

Sub testShapeRange()
    For Each target In Selection.ShapeRange
        MsgBox "target: " + target.Name + "," + TypeName(target)
        MsgBox "Selection.ShapeRange(1): " + _
            Selection.ShapeRange(1).Name + ", " + _
            TypeName(Selection.ShapeRange(1))
        MsgBox "ActiveSheet.Shapes(target.Name): " + _
            ActiveSheet.Shapes(target.Name).Name + "," + _
            TypeName(ActiveSheet.Shapes(target.Name))
    Next
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

This is because in these subs target is a ShapeRange. A ShapeRange does not have a TopLeftCell property. It does have a Name property...........this is why the second sub works.

Here is a way to get individual Shapes from a ShapeRange:

Sub durall()
    Dim s As Shape, i As Long
    For i = 1 To Selection.ShapeRange.Count
        Set s = Selection.ShapeRange(i)
        MsgBox s.Name
        MsgBox s.TopLeftCell.Address
    Next i
End Sub

Upvotes: 1

Related Questions