Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Accessing objects in a group

Is it possible to add to or delete objects from a group of drawing objects. If yes then what is the syntax?

This does not work

For Each shp In ActiveSheet.Shapes
For Each sshp In shp.GroupItems
Select Case Left(sshp.Name, InStr(sshp.Name, " ") - 1)
Case "Oval": sshp.Delete
End Select
Next sshp
Next shp
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 6
  • 5
1 Solution
 
Rgonzo1971Commented:
Hi,

i suppose it fails when there is no group
pls try

Sub Macro()

Dim sshpTemp As Object

For Each shp In ActiveSheet.Shapes
If shp.Type = msoGroup Then
For Each sshp In shp.GroupItems
Select Case Left(sshp.Name, InStr(sshp.Name, " ") - 1)
Case "Oval": sshp.Delete
End Select
Next sshp
End If
Next shp

End Sub

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
No, presently I have only one group in the drawing which contains some oval shapes. The line

sshp.Delete

does not work
0
 
Rgonzo1971Commented:
Hi,

Could send an example? the code works for me.

Regards
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
Which version of Excel is it?
0
 
Saqib Husain, SyedEngineerAuthor Commented:
2007
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Here is the file I am working on
Staff-layout.xlsx
0
 
Rgonzo1971Commented:
HI,

I am supposing that you cannot delete a shape which is in a group therefore my new code

Sub Macro()

Do
    Found = False
    For Each shp1 In ActiveSheet.Shapes
        If shp1.Type = msoGroup Then
            shp1.Ungroup
            Found = True
        End If
    Next
Loop While Found = True

For Each shp2 In ActiveSheet.Shapes
    Select Case Split(shp2.Name, " ")(0)
        Case "Oval"
            shp2.Delete
    End Select
Next shp2

ActiveSheet.Shapes.SelectAll
Selection.Group
End Sub

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Although this is not what I wanted to do, what happens if there are many groups?

I wonder why Rory asked for the version number. Is there any variation in a certain version?
0
 
Rgonzo1971Commented:
Hi,

In the case of many groups you would to have to to create arrays with the different elements of the groups before ungrouping to be able to regroup them

I think Rorya asked for the version because in XL 2010 it is possible to delete a shape in a group but I read a comment somewhere (with no mention of the version) that you could change the properties of a shape within a group but not deleting them.

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Can this be confirmed that it works in 2010? At the moment I do not have access to 2010.
0
 
Rgonzo1971Commented:
HI,

The code you sent in your question worked perfectly on my MS Office Plus 2010 version.

Regards
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Thanks for the info and for the effort.

Saqib
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now