Manipulating a Shape Object

After a Shape object is selected from the Shapes collection object, you can edit the shape through its properties and methods. As always, the properties and methods available are specific to the type of Shape object. Also, there may be properties and methods of subordinate objects available for editing. The following example adds a rectangle to the active worksheet and manipulates a few of its properties; the result is shown in Figure 10.4.

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100, 100, 50, 50).Select With Selection

.Name = "Red Square" .Left = 10 .Top = 10

End With

With ActiveSheet.Shapes("Red Square")

.Fill.ForeColor.RGB = RGB(255, 0, 0) .ZOrder msoBringToFront

End With

Adding a Shape object to a worksheet.

E2 Microsoft Excel - Bookl

Fit Edit View : Format loots

The AddShape() method of the Shapes collection object is used to add a rectangle to the drawing layer. In the preceding example, the constant msoShapeRectangle sets the shape type. The shape type is followed by four parameters that represent the Left, Top, Width, and Height properties of the AutoShape, respectively. After the shape is added to the drawing layer, its Name, Left, and Top properties are edited. The color of the shape is set as red (using the RGB() function) by returning a FillFormat object via the Fill property. Finally, the ZOrder() method of the Shape object is used to bring the shape to the front of the drawing layer.

Not all properties and subordinate objects are immediately available from an object selected using the Select() method. In the previous example, the Fill property and ZOrder() method are not available for the Shape object when it has been selected using the Select() method. Instead, another With/End With structure is needed to return the Shape object without selecting it before the Fill property and ZOrder() method can be applied.

The previous example illustrates some of the properties and methods common to most shapes. As is the case with the Chart object discussed in Chapter 9, some shapes and their subordinate objects have unique properties and methods that cannot be applied to all Shape objects. For example, the TextEffect property of the Shape object cannot be applied to shapes that do not contain text; therefore, when manipulating a shape through a VBA program, be careful to use the properties and methods that apply to that specific shape to avoid Run time errors.

Looping through a Collection of Shapes

Looping through a collection of Shape objects is essentially the same as looping through any other collection object. The code listed here loops through the Shapes collection object of the active worksheet. This is comparable to the methods discussed in earlier chapters for looping through worksheet cells contained within a range. An object variable is declared and used as the looping variable in a For/Each loop. The Shape collection object is returned using the Shapes property of the Worksheet object. As each Shape object is returned in the For/Each loop, it is tested for type via the Type property, and if the shape represents a line its name is copied to the worksheet.

Public Sub LoopThruShapes() Dim sh As Shape Dim I As Integer I = 1

For Each sh In ActiveSheet.Shapes If sh.Type = msoLine Then

Next End Sub

The preceding example represents one possible method for selecting and manipulating specific shapes from a collection. Next, you'll see a method for selecting a subset of Shape objects from a Shape collection using the ShapeRange collection object.

Sample code Listed in this chapter and a couple of additional examples illustrating the use of various Shape objects can be found in the ShapeDemos.xls Excel file on the CD-ROM that accompanies this book. Select different worksheets in the workbook to view the different demonstrations. The worksheet labeled Misc Shapes is shown in Figure 10.5.

Was this article helpful?

+1 -2
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


  • bellisima
    How to manipulate shapes in excel using vba?
    1 year ago
  • joel
    What is the difference between a shape and shaperange vba?
    6 months ago
  • Beatrice Mancini
    How to manipulate a shape excel vba?
    4 months ago
  • Senay Yohannes
    Can you find the rectangle shaped objects worksheet?
    8 days ago
  • negassi
    How to view shapes in excel?
    8 days ago

Post a comment