XL2007 Bug: ControlFormat in "For each"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Code using "ControlFormat" that worked in the previous version of Excel does
not work in Excel 2007.
In Excel 2007, start with a new worksheet, add an autoshape, such as an
oval, and select the shape. Run the following code (after removing the >'s
with Ctrl H).
Sub Play6()
Dim varShape As Variant
Dim shpS As Shape
Debug.Print Selection.ShapeRange(1).ControlFormat.LockedText
For Each varShape In Selection.ShapeRange
Debug.Print varShape.Name
Debug.Print varShape.ControlFormat.LockedText
Next varShape
End Sub
Click to expand...

The first and second print statements do fine. They print
True
Oval 1
The third produces an error:
"Object doesn't support this property or method."
This effectively makes ControlFormat unusable, because which shapes the user
selects can't be known in advance. (Anyone see a workaround?)
I also note that the variable varShape had to be declared as a variant. In
the previous version of Excel, it could be declared as a shape.
 
Here's a workaround: just change the For each... Next to For... Next. The
following works:
Sub Play7()
Dim shpS As Shape
Dim N As Integer
For N = 1 To Selection.ShapeRange.Count
Set shpS = Selection.ShapeRange(N)
With shpS
Debug.Print shpS.Name
Debug.Print shpS.ControlFormat.LockedText
End With
Next N
End Sub
Click to expand...

This solution to the problem seems general, so this bug doesn't seem so
critical as I originally thought.
 
Back
Top