[RESOLVED] Excel: select mult. shapes if their names contain a string

[pavelh]

Hi and thanks for reading my post!

I have [any] number of shapes in one sheet. They have names such as "BigShape 10", "BigShape 11", "MedShape 7", etc.

I need to select all shapes, whose name contain a certain string (for examle "gSh" from the example above).

Could anybody kindly help, please?


Many thanks in advance!!



Pavel

[RobDog888]

Welcome to the Forums.

Are you needing this in Excel VBA or via VB 6 automation?

[pavelh]

Thanks for your reply. I am using VB 6.1 from Office XP.

[MartinLiss]

Moved.

[RobDog888]

What type of shapes are they? All the same kind? How did you add them? From the Drawing toolbar, Controls toolbar, etc.

[pavelh]

They are all rectangles. I don't have the code with me at this moment, but I have written a macro that creates rectangles with appropriate size etc. then i have macro that changes selected shape according to its name. I just cannot figure out how to select several shapes (rectangles in my case) that have a certain string in their (custom) name.

[westconn1]

Sub selectshapes()
Dim s As Shape, myarr(), n As Integer
ReDim myarr(Sheet4.shapes.Count)
For Each s In Sheet4.shapes
    If Not InStr(s.Name, "Combo") = 0 Then
         myarr(n) = s.Name
         n = n + 1
        End If
Next
ReDim Preserve myarr(n - 1)
    Sheet4.shapes.Range(myarr).Select
 
End Sub

this works for comboboxes, so should wor for your rectangles

[pavelh]

This is it! Many, many thanks!!
I only had about 30 minutes to play with your code so far. It uses stuff I haven't use before, so I will read about it first. I also had to change sheet4 to ActiveSheet for some reason (renaming didn't work), but it already does what I needed so much. It selects the shapes with the string ("Combo" in your code). It seems like I will be using your code almost every day from now on. You really helped a lot! A lot!
All the best wishes!!
Pavel