Ungrouping shapes

[Carey Carlan]

Using Visual Basic 6 (not VBA) and Excel 2000

I have graphics and text grouped together on a worksheet.
I need to update the text.
Trying to ungroup in the follow code I get run time erro 1004 "Application-
defined or object-defined error" from Excel back to VB.

For Each xlWorkSheet In xlWorkbook.Sheets
xlWorkSheet.Select
For I = 1 To xlWorkSheet.Shapes.Count
Shape = xlWorkSheet.Shapes(I)
If Shape.Type = msoGroup Then
Shape.Select
Shape.Ungroup <-- crash and burn here.
End if
Next I
Next xlWorkSheet

help?

 

[Jim Cone]

Don't use variables that are spelled the same as terms Excel already
has defined... try shp instead of Shape.

Declare your variables with the appropriate data type...
Dim shp as xlApp.Shape

You must "Set" an object (shp) after declaring it so that
Excel knows what it refers to. Or you can use it in a For/Next
loop and Excel will make an implied reference for you...
'--
For Each shp in xlWorkSheet.Shapes
If shp.Type = msoGroup then shp.Ungroup
Next
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Carey Carlan"
<[email protected]>
wrote in message
Using Visual Basic 6 (not VBA) and Excel 2000

I have graphics and text grouped together on a worksheet.
I need to update the text.
Trying to ungroup in the follow code I get run time erro 1004 "Application-
defined or object-defined error" from Excel back to VB.

For Each xlWorkSheet In xlWorkbook.Sheets
xlWorkSheet.Select
For I = 1 To xlWorkSheet.Shapes.Count
Shape = xlWorkSheet.Shapes(I)
If Shape.Type = msoGroup Then
Shape.Select
Shape.Ungroup <-- crash and burn here.
End if
Next I
Next xlWorkSheet

help?