Odd Shape Selection Behavior
In Excel 2003, open a new workbook and add this code.
Sub TestShapes()
Dim i As Long
Dim sType As String
Dim shp As Shape
Dim s1 As String, s2 As String
For Each shp In Sheet1.Shapes
shp.Delete
Next shp
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s1, s2)).Select
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
Debug.Print
Sheet1.Shapes.Range(Array(s2, s1)).Select
Dim i As Long
Dim sType As String
Dim shp As Shape
Dim s1 As String, s2 As String
For Each shp In Sheet1.Shapes
shp.Delete
Next shp
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s1, s2)).Select
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
Debug.Print
Sheet1.Shapes.Range(Array(s2, s1)).Select
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
Debug.Print
Sheet1.Shapes.Range(Array(s2, s1)).Select
sType = TypeName(Selection(1))
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
End Sub
It adds a chart object and a rectangle, selects them in different orders, then reads the selection. I get this
If I select the chart first, it returns both shapes as expected. If I select the rectangle first, it returns the rectangle twice. If I select the rectangle first, but check the TypeName of Selection(1), it slaps Excel in the head so that it realizes what’s selected.
Am I missing something here?
It looks like that the first time you do a print.debut it is reporting the based on the order of the array and after that it reports based on the order the shapes were created.
So if we had three shapes:
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
s3 = Sheet1.Ovals.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s1, s2, s3)).Select
Debug.Print
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
It looks like that the first time you do a debug.print it is reporting based on the order of the array and after that it reports based on the order the shapes were created.
So if we had three shapes:
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
s3 = Sheet1.Ovals.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s2, s3, s1)).Select
Debug.Print Selection(2).Name
Debug.Print
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
You would get “Oval” first (because that is in position 2 of the array and then “Chart, Rect, Oval” because they were created in that order.
This also means the first debug.print loop looks correct but is in fact “incorrect”. It’s only because the array is selected in the same order the shapes were created that it gives a correct result.