XL2007 Bug: ControlFormat in "For each"

C

Christopher King

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

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.
 
C

Christopher King

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top