Macro to Select All Shapes in a Specific Range

[ndouglas48]

Hello all! I am trying to create a macro that simply selects all shapes in a specific range, let's say A1:D5. I am able to select all shapes in a given worksheet but need to narrow this to the specific range. Here's the kicker: How can I do this without referencing the specific shape names, i.e. "Oval 4"? I am using macros to create and delete shapes so the names continually change. So this macro needs to search the range and select all shapes that are present within it. Any help is hugely appreciated. Thank you!

ND

 

[JLGWhiz]

Hello all! I am trying to create a macro that simply selects all shapes in a specific range, let's say A1:D5. I am able to select all shapes in a given worksheet but need to narrow this to the specific range. Here's the kicker: How can I do this without referencing the specific shape names, i.e. "Oval 4"? I am using macros to create and delete shapes so the names continually change. So this macro needs to search the range and select all shapes that are present within it. Any help is hugely appreciated. Thank you!

ND

Since shapes are not properties of the Range object, the task will be quite difficult. Usually shapes are handled as separate entities when manipulating, coloring, and sizing.

 

[Rick Rothstein]

Hello all! I am trying to create a macro that simply selects all shapes in a specific range, let's say A1:D5.

Do the shapes have to be wholly contained within the range, or will just intersecting them be okay?

 

[ndouglas48]

I believe just intersecting them would be okay. I have the following vba that does this but it deletes the shapes. Not sure if there is a way to change this so it just selects them instead of deleting.

Dim shp As Shape


For Each shp In ActiveSheet.Shapes

If Not Intersect(shp.TopLeftCell, Range("A1:D5")) Is Nothing Then

shp.Delete
End If
Next shp
Range("A1").Select

 

[pgc01]

Hi

Can you tell us what you need to do? It's not usually necessary to select objects in vba.

For ex., in the code you posted you can see that no shape is selected and you do what you need to do.

 

[ndouglas48]

I am wanting to create an area where someone could draw using excel shapes and can easily select all objects that are drawn in that area without having to manually click on each one. The drawing area is rather large and because shapes will always be different I cannot rely on using specific names to identify these shapes, i.e. "Oval 1".

 

[ndouglas48]

The - Activesheet.Shapes.SelectAll - would work perfectly if I could specify the range.

 

[pgc01]

Try for the active sheet:

Sub SelectShapes()
Dim shp As Shape
Dim r As Range

Set r = Range("A1:D5")

For Each shp In ActiveSheet.Shapes
    If Not Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), r) Is Nothing Then _
        shp.Select Replace:=False
Next shp
End Sub

 

[ndouglas48]

pgc01 you da man! That worked perfectly! I appreciate it so much!

 

[ndouglas48]

Is there something I could add to this that would automatically group these selected objects after selection?