VBA Excel: Selection collection looped index references incorrect range when used with shapes

Consider a Worksheet containing a number of Shapes, and a snippet (1):

'(1)
For i = 1 To Selection.Count
    MsgBox Selection(i).Name
Next

Regardless of which Shapes are selected (must be more than 1 of course) the first two Shapes seem to always be returned.

Consider use of ShapeRange (2):

'(2)
For i = 1 To Selection.Count
 开发者_JS百科   MsgBox Selection.ShapeRange(i).Name
Next

This (2) actually returns the name of the selected Shape, as does (3):

'(3)
For i = 1 To Selection.ShapeRange.Count
    MsgBox Selection.ShapeRange(i).Name
Next

While (1) produces an error if less than 1 Shape is selected, (3) appears to work fine - unless a Chart is the sole selection, upon which an Automation Error is returned: "The object invoked has disconnection from its clients."

I don't understand why the first fails to return the correct shape in general - I'm assuming that the Selection collection is more complex than meets the eye, and I also don't understand why Selection.ShapeRange.Count fails when one Chart is selected, but succeeds when one Shape is.

Would greatly appreciate any light shed on this

---

I have one chart and five rectangles on a sheet and I'm using Excel 2003.

If I select a rectangle first, then the chart, the (1) code works for me without error. It returns the names of the two shapes I selected as I would expect.

If I select the chart first, then a rectangle, it returns the chart twice as if the chart is both of the selected shapes. I can only assume this is a bug. But a strange bug because if I run the code again without changing the selection, it returns the same as if I had selected the rectangle first. Odd.

If less than one shape is selected, that means a range is selected. Something is always selected in Excel, so if you don't have any shapes selected, it's likely your Selection object refers to a Range object. You can use

Typename(Selection)

to determine what is selected. If you do have a Range selected, and that range doesn't have defined name, the Name property will return an error.

It is very difficult (impossible?) to select a "chart shape" in Excel. Check out the selection points when you select a chart by itself - they're black squares. Now hold down the control key and select another shape. The selection points on the chart turn to white circles. When it's selected by itself, the Selection object is actually a ChartArea object. Excel "guesses" that when you select a chart you really want to select a component of a chart (the ChartArea by default). Because the ChartArea doesn't have a ShapeRange property, you get an error.

As to the first assumed bug, here is some code that proves it out. Note in the third section, I check the Typename, but don't do anything with it. This seems to jolt Excel into knowing what is actually selected

Sub testshapes()

    Dim i As Long
    Dim sType As String

    Sheet1.Shapes.Range(Array("Rectangle 5", "Chart 6")).Select

    For i = 1 To Selection.Count
        Debug.Print Selection(i).Name
    Next i

    Sheet1.Shapes.Range(Array("Chart 6", "Rectangle 5")).Select

    For i = 1 To Selection.Count
        Debug.Print Selection(i).Name
    Next i

    Sheet1.Shapes.Range(Array("Chart 6", "Rectangle 5")).Select

    sType = TypeName(Selection(1)) 'avoids chart selected first bug
    For i = 1 To Selection.Count
        Debug.Print Selection(i).Name
    Next i

End Sub

In the first section, I get the right answer. In the second section, I get "Chart 6" for both debug.print statements. In the third section, I get the right answer.

I'm not sure if this totally answers your question, but I hope it gets you closer.

---

The Selection can contain objects other than shapes. Not all of the objects in the selection will have a name property. That's probably why you're getting errors with more than one shape selected - because you're selecting more than just shapes. The proper way to scan all selected shapes is #3 as discussed here.

The "object invoked" error sounds like an Excel bug. I would simply catch the error, handle it, and look to the MS knowledge base for guidance on how to work around it and/or minimize its occurrence.