CODEWITHSUNDEEP
excelshapesvba
Rick
Rick

Reputation: 45311

Excel VBA: How to add all of the current shapes on a worksheet to a ShapeRange?

Somewhat of a VBA newbie here.

It is clear to me how to create a ShapeRange using individual or multiple Shape objects:

Dim sht As Worksheet
Set sht = MySht
'
'*Add some shapes*
'
Dim shprng As ShapeRange
Set shprng = sht.Shapes.Range(Array(1,2,3))

Is there a way to add ALL of the currently existing shapes on a worksheet to shprng? In other words, is there a method to return a ShapeRange from a Shapes object...? Something like this:

Set shprng = sht.Shapes.Range.SelectAll '<--- Does not work: Type Mismatch
Set shprng = sht.Shapes                 '<--- Same error
Set shprng = sht.Shapes.Range           '<--- Error: Argument not optional

Thanks!

Upvotes: 2

Views: 12319

Answers (2)

Ovichan
Ovichan

Reputation: 86

In my Office 365, the code of TmDean did not work. It was necessary explicitly declare of variable as dinamic array.

    Dim shape_index() As Variant    'Dim shape_index() as Long
    Dim i As Long

    ReDim shape_index(1 To sht.Shapes.Count)
    For i = 1 To UBound(shape_index)
        shape_index(i) = i
    Next

    Set shprng = sht.Shapes.Range(shape_index)

Upvotes: 1

Tmdean
Tmdean

Reputation: 9309

If you want to create a ShapeRange by selecting all the shapes on a sheet, you would first select them then get the ShapeRange from the Selection object.

sht.Shapes.Range.SelectAll
Set shprng = Selection.ShapeRange

I usually prefer not to use the Selection object in VBA because it tends to be flaky and can cause errors in weird situations. I think a better way to do this is to build an array of Shape indexes and get the ShapeRange using this array.

Dim shape_index As Variant
Dim i As Long

ReDim shape_index(1 To sht.Shapes.Count)
For i = 1 To UBound(shape_index)
    shape_index(i) = i
Next

Set shprng = sht.Shapes.Range(shape_index)

Upvotes: 6

Related Questions