The Shape Range Collection Object

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The ShapeRange collection object represents a collection of Shape objects that may contain all, some, or just one of the Shape objects in the drawing layer of a worksheet. A ShapeRange collection object can be constructed from the current shapes using any of several criteria defined in decision structures (If/Then). For example, a ShapeRange collection object could be constructed out of just those shapes that are of type AutoShape, or perhaps only those Shape objects that are lines.

If you want to return all selected Shape objects to a ShapeRange collection object, use the ShapeRange property of the Selection object when it represents a group of selected Shape objects.

TheMisc Shapes worksheet from the ShapeDemos.xls workbook.

TheMisc Shapes worksheet from the ShapeDemos.xls workbook.

Shapes Excel

ActiveSheet.Shapes.SelectAll Selection.ShapeRange.Rotation = 30 Selection.ShapeRange(1).Rotation = 60

The first line selects all Shape objects in the active worksheet. The second line sets the angle of rotation to 30 degrees for all selected Shape objects. The third line sets the angle of rotation to 60 degrees for the first Shape object that was added to the collection (out of those objects currently selected).

To return a subset of the Shape objects as a ShapeRange collection object, use the Range property of the Shapes collection object.

ActiveSheet.Shapes.Range(1).Select ActiveSheet.Shapes.Range("Line 1").Select ActiveSheet.Shapes.Range(Array(1, 2, 3, 4)).Select ActiveSheet.Shapes.Range(Array("Line 1", "WordArt 2")).Select

The Range property of the Shapes collection object accepts an integer, string, or parameter array as arguments. A parameter array specified with the Array() function is more practical because the Range property is not needed to select a single shape from the Shapes collection object. The parameter array may contain a list of integers representing the index values of the

Shape objects or strings representing their names. Alternatively, you can build a parameter array holding the integers or strings representing specific objects based on various conditions. Consider the following procedure used to select all the lines in the drawing layer of the active worksheet:

Public Sub SelectLines() Dim sh As Shape Dim lineNames() As Variant Dim numLines As Integer Dim ws As Worksheet

Set ws = ActiveSheet For Each sh In ws.Shapes

If sh.Type = msoLine Then

ReDim Preserve lineNames(numLines) lineNames(numLines) = sh.Name numLines = numLines + 1 End If

Next ws.Shapes.Range(lineNames).Select Selection.ShapeRange.Line.Weight = 4.5 End Sub

The SelectLines() procedure uses a For/Each loop to iterate through the Shapes collection object for the active worksheet and build a parameter array (lineNames declared as variant) containing the names of the Shape objects of type msoLine. The name of each object of type msoLine is copied to the lineNames array for later use.

Next, the parameter array is passed to the Range property of the Shapes collection object, and objects of type msoLine are returned and selected. Additional code can now be added to modify the selected shapes. In this example, the ShapeRange property is used to return all the selected shapes and set the thickness of the lines via the Weight property.

Figure 10.6 shows the result of applying the preceding procedure to the shapes contained in the worksheet displayed in Figure 10.5.

The preceding procedure represents a useful method for selecting a range of Shape objects of a particular type when you don't know the proper names or index values at Design time.

TheMisc Shapes worksheet after execution of the SelectLines() sub procedure.

TheMisc Shapes worksheet after execution of the SelectLines() sub procedure.

Was this article helpful?

+1 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


  • aatifa luwam
    9 years ago
  • katie-leigh williamson
    How to use shape range by vba?
    1 year ago
  • Filmon
    Can we define a shaperange vba?
    12 months ago
  • wolfgang
    How to delete the shapes in particular range in vba?
    11 months ago
  • ermias
    How to define a shape range?
    10 months ago
  • janina
    What is shaperange in excel shapes?
    7 months ago
  • Hob
    How to parametrize all shapes of a collection vba?
    3 months ago
  • cora
    How to group all shapes of a collection vba?
    3 months ago

Post a comment