The shapes collecnon and shape objects

The Shapes collection object represents all Shape objects in the drawing layer of the worksheet. The Shapes property of the Worksheet object is used to return the entire collection of Shape objects in the drawing layer. The following line of code uses the Count property of the Shapes collection object to return the total number of shapes in the drawing layer of the active worksheet:


You can think of the drawing Layer as a sheet of clear plastic cellophane draped over the top of the worksheet; therefore, shapes added to the drawing layer are positioned on top of the worksheet and mask the cells underneath. The masked cells can still be used to hold data.

Like other collection objects, an index or name can be specified to return a single Shape object from the collection. To return a Shape object by index, specify a number.


Or, to return a Shape object by name, include the name in quotes.

ActiveSheet.Shapes("Oval 1").Select

To add a shape to a worksheet, use one of several Add() methods of the Shapes collection object. For example, to add a line, use the AddLine() method.

ActiveSheet.Shapes.AddLine(10, 100, 250, 500).Select

The AddLine() method accepts four parameters for the starting and ending x- and y-values representing the x, y-coordinate pairs of the two points used to define the line. The coordinates are specified in points relative to the upper-left corner of the worksheet. In the preceding example, a line is drawn on the active worksheet from point x = 10,y = 100 to the point x = 250, y = 500.

The Add() methods of the Shapes collection object also return a reference to the newly added Shape object, so it is possible to immediately apply a property or method to the shape in the same statement. It is often convenient to select the object then use a With/End With structure to manipulate several properties of the object. You'll see an example of this in the section "Manipulating a Shape Object."

Other Add() methods of the Shapes collection object include AddShape(), AddPicture(), AddOLEObject(), and AddPolyline(), to name just a few. The AddShape() method refers to the AutoShapes found on the Drawing toolbar (see Figure 10.2). The example that follows adds a triangle to the active worksheet and selects it:

ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 230, 220, 25, 20).Select

The AddShape() method requires five parameters representing, in order, the shape type (a VBA defined constant, msoShapeIsoscelesTriangle in the example), and the Left, Top, Width, and Height properties of the object.

Drawing * x

Lines *

H Connectors >

Sa Flowchart '

stars and Banners *

Callouts > Hore Autoshapes...


The Drawing


The Drawing

All of the Add() methods are implemented in a manner similar to that of the AddShape() method, but the required parameters are specific to the shape type. You will see more examples of different shape types in the remainder of the chapter. For details about each method and the parameters it requires, consult the online help or the members of the Shapes collection in the Object Browser (see Figure 10.3).

The Object Browser showing members of the Shapes collection object.

The Object Browser showing members of the Shapes collection object.

Was this article helpful?

0 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

Post a comment