Working with Shape Objects | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

So far, this chapter has focused exclusively on modifying the color of a range. This section provides examples of changing colors in Shape objects. In Excel, use the Insert Illustrations Shapes group to add a shape to a worksheet.

Figure 30-7 shows a Shape object inserted on a worksheet. This object's default name is Right Arrow 1. The number in the name varies, depending on how many shapes you have inserted. For example, if you had previously inserted two other shapes (of any style), the name would be Right Arrow 3.

Figure 30-7: A Shape object on a worksheet.

A shape's background color

The background color of a Shape object is determined by the RGB property. So, to get the decimal color value of this shape, use a statement like this:

 MsgBox ActiveSheet.Shapes("Right Arrow 1").Fill.ForeColor.RGB

This statement may be a bit confusing, so I'll break it down. The Fill property of the Shape object returns a FillFormat object. The ForeColor property of the FillFormat object returns a ColorFormat object. So the RGB property actually applies to the ColorFormat object, and this property contains the decimal color value.

Note

If you're confused about the use of the ForeColor property in this example, you're not alone. Most people, myself included, would expect to use the BackColor property of the FillFormat object to change the background color of an object. As it turns out, the BackColor property is used for the second color if the object is shaded or filled with a pattern. For an unfilled Shape with no pattern, the ForeColor property controls the background color.

When working with Shape objects, you almost always want your code to perform multiple actions. Therefore, it's efficient to create an object variable. The code that follows creates an object variable named Shp :

 Dim Shp As Shape Set Shp = ActiveSheet.Shapes("Right Arrow 1") MsgBox Shp.Fill.ForeColor.RGB

Tip

An additional advantage to creating an object variable is that you can take advantage of the VBE's Auto List Members feature, which displays the possible properties and objects as you type (see Figure 30-8). This is particularly helpful in the case of Shape objects because actions you take with Shapes are not recorded by Excel's macro recorder.

Figure 30-8: Typing a statement with the assistance of the Auto List Members feature.

If you'll be working only with the shape's colors, you can create an object variable for the shape's ColorFormat object, like this:

 Dim ShpCF As ColorFormat Set ShpCF = ActiveSheet.Shapes("Right Arrow 1").Fill.ForeColor MsgBox ShpCF.RGB

The RGB property of the ColorFormat object controls the color of the shape. Following are some additional properties. If you're not familiar with document theme colors, see "Understanding document theme colors," earlier in this chapter.

ObjectThemeColor : A number between 1 and 10 that represents the theme color (that is, a color in the first row of the 10-x-6 theme color grid)
SchemeColor : A number that ranges from 0 to 80 that represents the color as an index in the current color scheme. These are colors from the old 56-color palette, and I don't see any need to ever use the SchemeColor property.
TintAndShade : A number between “1 and +1 that represents the tint or shade of the theme color.
Type : A number that represents the ColorFormat object type. As far as I can tell, this read-only property is always 1 , which represents the RGB color system.

Changing the background color of a shape does not affect the shape's outline color. To modify the color of a shape's outline, access the ColorFormat object of the shape's LineFormat object. The following statements set a Shape's background color and outline to red:

 Dim Shp As Shape Set Shp = ActiveSheet.Shapes("Right Arrow 1") Shp.Fill.ForeColor.RGB = RGB(255, 0, 0) Shp.Line.ForeColor.RGB = RGB(255, 0, 0)

Here's an alternative way to accomplish the same effect, using object variables :

 Dim Shp As Shape Dim FillCF As ColorFormat Dim LineCF As ColorFormat Set Shp = ActiveSheet.Shapes("Right Arrow 1") Set FillCF = Shp.Fill.ForeColor Set LineCF = Shp.Line.ForeColor FillCF.RGB = RGB(255, 0, 0) LineCF.RGB = RGB(255, 0, 0)

Using other fill types with a shape

Shapes can also display other types of fills, such as gradients, pictures, and textures. The examples in this section demonstrate how to apply these other types of fills to a Shape object.

CD-ROM

All the examples in this section are available on the companion CD-ROM. The filename is shape object colors.xlsm .

The following code creates a rectangle, hides its border, and applies a two-color gradient. One of the colors is set specifically ; the other color is one of the document theme colors. Figure 30-9 shows the result of running this macro.

Figure 30-9: A Shape object with a two-color gradient fill.

 Sub MakeShapeWithGradient()     Dim Shp As Shape '   Create the shape     Set Shp = ActiveSheet.Shapes.AddShape( _         Type:=msoShapeRectangle, _         Left:=100, _         Top:=10, _         Width:=200, _         Height:=100) '   Hide the border     Shp.Line.Visible = False '   Add 2-color gradient     With Shp.Fill         .TwoColorGradient _            Style:=msoGradientVertical, Variant:=2         .ForeColor.RGB = RGB(255, 255, 255) 'white         .BackColor.ObjectThemeColor = msoThemeColorAccent4     End With End Sub

The code that follows creates a Shape that uses pattern fill. See Figure 30-10.

Figure 30-10: This Shape uses a diamond-pattern fill.

 Sub MakeShapeWithPattern()     Dim Shp As Shape '   Create the shape     Set Shp = ActiveSheet.Shapes.AddShape( _         Type:=msoShapeNotchedRightArrow, _         Left:=100, _         Top:=10, _         Width:=200, _         Height:=100) '   Hide the border     Shp.Line.Visible = False '   Add a pattern     With Shp.Fill         .Patterned Pattern:=msoPatternSolidDiamond         .ForeColor.RGB = RGB(0, 0, 0) 'black         .BackColor.RGB = RGB(255, 255, 255) 'white     End With End Sub

The next procedure is similar, but it uses a picture for the Shape's background. The graphic file is loaded from the disk. The code also adds a reflection to the Shape. Figure 30-11 shows the result.

Figure 30-11: A Shape that uses a picture.

 Sub MakeShapeWithPicture()     Dim Shp As Shape '   Create the shape     Set Shp = ActiveSheet.Shapes.AddShape( _         Type:=msoShapeOctagon, _         Left:=100, _         Top:=10, _         Width:=250, _         Height:=196) '   Hide the border     Shp.Line.Visible = False '   Add a picture     Shp.Fill.UserPicture _         ThisWorkbook.Path & "\weirddog.jpg" '   Give it a reflection     Shp.Reflection.Type = msoReflectionType5 End Sub

The next Shape object example creates a shape with a texture and applies some 3-D effects and a shadow. Figure 30-12 shows the result.

Figure 30-12: A shape with a texture fill and a few other accoutrements.

 Sub MakeShapeWithTexture()     Dim Shp As Shape '   Create the shape     Set Shp = ActiveSheet.Shapes.AddShape( _         Type:=msoShapeCross, _         Left:=100, _         Top:=10, _         Width:=200, _         Height:=200) '   Hide the border     Shp.Line.Visible = False '   Give it a texture     Shp.Fill.PresetTextured _        PresetTexture:=msoTextureBlueTissuePaper '   Some 3D effects     With Shp.ThreeD         .Visible = True         .Depth = 60         .BevelTopDepth = 10         .RotationY = 10     End With  '   And a shadow     With Shp.Shadow         .Visible = True         .Transparency = 0.8         .OffsetX = 30         .OffsetY = 30         .Blur = 12     End With End Sub

The final Shape example involves text. The procedure adds a shape, sets the shape's background, adjusts the border, adds text, and then formats the text - very similar to what happens when you insert a WordArt object. That's because there's really nothing special about WordArt. When you use the Insert Text WordArt command, you're just inserting a shape that has text. Your code can manipulate WordArt just as it can manipulate Shape objects.

 Sub MakeShapeWithText()     Dim Shp As Shape '   Create the shape     Set Shp = ActiveSheet.Shapes.AddShape( _         Type:=msoShapeRectangle, _         Left:=200, _         Top:=10, _         Width:=200, _         Height:=100) '   Shape's background color     Shp.Fill.ForeColor.ObjectThemeColor = msoThemeColorLight2     Shp.Shadow.Visible = True '   Shape's border     Shp.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2     Shp.Line.Weight = 6 '   Add text     Shp.TextFrame2.TextRange.Text = "Microsoft Excel" '   Format the text     With Shp.TextFrame2.TextRange.Font         .Size = 38         .Bold = True         .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2         .Shadow.Visible = True     End With End Sub

Figure 30-13 shows the result of executing the MakeShapeWithText procedure.

Figure 30-13: A Shape object that contains text.

Learning more about shapes

The information in this section barely scratches the surface when it comes to working with Shape objects. Programming shapes with VBA could easily serve as the subject matter for a complete book.

To learn more about Shape objects, use the Object Browser (press F2, in the VBE), the Help system, the macro recorder (which is of limited value), and the Internet. And don't forget the best learning tool of them all: experimentation.