Exploring Graphical Objects


Graphical objects float on a layer above the worksheet grid. The graphical capabilities of Excel have always been very good, and with Excel 2007 they verge on phenomenal. You could practically use Excel as a full-fledged graphics program. Unfortunately, the macro recorder does not yet work with these newly enhanced shape objects, so this section will be a good exercise for your navigational skills. Fortunately, the macro recorder does work with some of the enhanced worksheet cell formatting and can provide clues that you can use in the uncharted territory.

Use Worksheet Cells as a Drawing Grid

Many graphics programs have a “snap to grid” feature to help the designer align objects optimally, and typically it is possible to adjust the scale of the grid. Excel also has a snap-to-grid feature-it’s the grid of cells on the worksheet. When working with graphics, it’s useful to be able to control the size of the grid and to use the grid to align objects.

  1. In Excel, activate the Shapes tab of the Chapter06 workbook, and then in the Visual Basic Immediate window, type Cells.RowHeight = 72 and press Enter.

    The height of all the rows on the worksheet changes to 1 inch tall. You specify the height of a row by using points, and there are 72 points in 1 inch.

  2. Type Cells.ColumnWidth = 13 and press Enter.

    The width of all the columns on the active sheet changes to 1 inch wide. You specify the width of a column by using the average width of the zero (0) character in the standard 10-point Arial font-the font used by the Normal cell style. It just so happens that in this font, 13 zero (0) characters fit into 1 inch.

    image from book

  3. Place the insertion point in the word ColumnWidth, and press the F1 key.

    The Help topic for the ColumnWidth property of a Range object appears. The Help topic explains the unique qualities of the property. Because ColumnWidth is a property of a Range object, at the bottom of the page there is a link to the complete list of members for the Range object.

    image from book

  4. Click the Range Object Members link.

    A page appears with descriptions and links for all the methods and properties available for a Range object. Each object in the Excel object model has a similar page of members.

    image from book

Help is an important tool, particularly when navigating in unfamiliar territory. One particularly valuable feature of Help is the annotated list of members for an object. Auto Lists or the Object Browser can give you the list of members, but it is easier to scan for a new method or property by using Help.

Add a Gradient Fill to a Cell

Traditionally, Excel cells have contained solid colors. Excel 2007 allows you to add gradient fills and other special formatting to cells. The ability to control extended cell formatting from a macro is useful in its own right, but recording and creating cell gradients can also help you understand how to add gradients to other shapes.

  1. In Excel, start recording a macro named rCellGradient. Right-click cell B1, and click Format Cells. On the Fill tab, click Fill Effects. Under Shading Styles, select Vertical. Leave the first Variant selected, and then click OK twice.

    image from book

  2. Stop the recorder, and edit the macro. It should look like this:

    Sub rCellGradient()   Range("B1").Select   With Selection.Interior   .Pattern = xlPatternLinearGradient   .Gradient.Degree = 0   .Gradient.ColorStops.Clear   End With   With Selection.Interior.Gradient.ColorStops.Add(0)   .ThemeColor = xlThemeColorDark1   .TintAndShade = 0   End With   With Selection.Interior.Gradient.ColorStops.Add(1)   .ThemeColor = xlThemeColorAccent1   .TintAndShade = 0   End With  End Sub 

    The critical part of this macro is setting the Pattern property. You can accomplish the same effect in a single step.

    See Also 

    For more details about theme colors, see the section titled “Record a Macro to Customize a PivotTable Style” in Chapter 5, “Explore Data Objects.”

  3. Select cell B2. Then in Visual Basic, create the following macro shell, and press F8 three times to initialize the variable.

    Sub CellGradient()   Dim myInterior as Interior   Set myInterior = Selection.Interior End Sub 

    When you declare an object variable, you can set it at the most useful level. If you’ll be working mostly with cells, you can create a Range object variable. If you’ll be working mostly with a subordinate object, you can make the rest of the macro simpler by creating an object variable specifically for the lower-level object.

  4. In the Immediate window, type myInterior.Pattern = xlPatternLinearGradient and press Enter.

    Cell B2 changes to have the same gradient fill as cell B1. The recorder includes many statements that simply set default properties. But, fortunately, those extra statements show you a lot about how a gradient works. One of the extra statements includes a property called GradientStops. As with most collections, you can specify a single item from the collection.

    image from book

  5. Type myInterior.Gradient.ColorStops(1).ThemeColor = xlThemeColorAccent6 and press Enter.

    The left side of the gradient changes from white to orange. The Fill Effects dialog box allows you to choose only a two-color gradient, but ColorStops is a collection that you can add to.

  6. Type myInterior.Gradient.ColorStops.Add 0.75 and press Enter.

    A new, white stripe appears in the gradient, about three quarters of the way to the right within the cell. There are now three items in the ColorStops collection. When you add a new item to the ColorsStops collection, you must give the new item a position between 0 and 1. The items in the collection are then sorted by position, so to access the new item, you use 2 as the index, not 3.

  7. Type MyInterior.Gradient.ColorStops(2).Position = 0.25 and press Enter.

    The white stripe moves to approximately one quarter of the way to the right within the cell.

    image from book

  8. Press F5 to end the macro. Copy the contents of the Immediate window and paste it into the CellGradient macro. In Excel, select cell B3, and then in Visual Basic, press F8 repeatedly to test the macro.

  9. In Excel, select the range C1:E2, and then in Visual Basic, press F5 to test the macro.

    Each cell is formatted individually. In Excel, formats such as gradients are applied one cell at a time.

    image from book

    The finished macro should look like this:

    Sub CellGradient()   Dim myInterior As Interior   Set myInterior = Selection.Interior      myInterior.Pattern = xlPatternLinearGradient   myInterior.Gradient.ColorStops(1).ThemeColor = xlThemeColorAccent6   myInterior.Gradient.ColorStops.Add (0.75)   myInterior.Gradient.ColorStops(2).Position = 0.25 End Sub 

The macro recorder can help you start to understand the methods and properties for dealing with cell gradients. However, you can do many things from a macro statement that you cannot do in the user interface, so those things will never appear in a recorded macro. For example, in the user interface, you cannot create multiple color stops in the gradient for a cell, and you can’t adjust the position of a color stop within the gradient. You can do both in a macro.

Tip 

In Excel 2007, conditional formatting works very much the same as gradients. Each conditional format is part of a collection that you can manipulate. By applying a conditional format while recording a macro, you can see the critical property names.

Add a Gradient-Filled Shape

Cells are rigidly rectangular, and when you apply a gradient fill to a range of cells, a separate gradient fills each cell. By using shapes, you can create graphics with much more flexibility. The methods and properties for creating and formatting a Shape object are not quite the same as those for a Range object, but there are enough similarities that you can use Help and other techniques to navigate through the details. By using macro statements to create a rectangular Shape object and then apply the same format you gave the worksheet cell, you can compare the way a gradient works with a shape with the way it works with a range.

  1. Create the following shell of a macro, and press F8 twice to initialize the variables.

    Sub ShapeGradient()   Dim myShapes as Shape   Dim myFill as FillFormat End Sub
  2. In the Immediate window, type Set myShape = Sheet1.Shapes.AddShape( msoShapeRectangle, 0, 0, 72, 72) and press Enter.

    image from book

    A rectangle appears, exactly filling cell A1. The name Sheet1 happens to be the code name for the Shapes worksheet. By using the code name, you can get Auto List Help as you construct the statement. If you want the macro to create a shape on any worksheet, replace the code name with ActiveSheet after you finish testing the macro. The top, left, height, and width values for a shape are all specified in points-with 72 points to 1 inch.

    Tip 

    To identify the code name for the active worksheet, in the Immediate window, type ?ActiveSheet.CodeName and press Enter.

  3. Type Set myFill = myShape.Fill and press Enter.

    The name of an object sometimes differs from the name of the property that returns the object. In this case, the Fill property returns a FillFormat object. The FillFormat of a shape is analogous to the Interior object of a range.

    Tip 

    You can find the object returned by a property in several ways. To use Help, click the property name-in a statement or in the Object Browser-and press F1 to see the topic for the property. The property topic contains a link to the object it returns, and the object topic contains a link to the annotated list of members for that object. (If a link in Help is broken, try typing the object name in the Search box.) In addition, you can type the TypeName function along with the expression that uses the property in the Immediate window. For example, to find the name of the object for the Fill property of a shape named myShape, you could type ?TypeName(myShape.Fill).

  4. Type myFill.TwoColorGradient msoGradientVertical,1 and press Enter.

    The shape becomes filled with a vertical gradient similar to that of the worksheet cell, but the colors are reversed. With a range, you add a gradient by assigning a value to the Pattern property. With a shape, you execute a method to get the same effect.

  5. Type myFill.GradientStops(1).Color.ObjectThemeColor = msoThemeColorAccent6 and press Enter.

    The left side of the rectangle changes to the same color as the left side of the cell-orange in the default theme. With a range, you use the ColorStops property to specify the parts of the gradient. The name of this property is similar enough to GradientStops that simply by using Auto Lists, you should be able to discover the new property. With a shape, you also split Color out into a separate object and use ObjectThemeColor instead of simply ThemeColor.

  6. Type myFill.GradientStops(2).Color.ObjectThemeColor = msoThemeColorAccent1 and press Enter.

    The right side of the rectangle changes to the same color as the right side of the cell-blue in the default theme.

  7. Type myFill.GradientStops.Insert rgbWhite,0.75 and press Enter.

    A white band appears three-quarters of the way to the right of the rectangle. With a cell, you use the Add method to add a new value to the ColorStops collection. With a shape, you use the Insert method to add a new value to the GradientStops collection.

  8. Type myFill.GradientStops(3).Position = 0.25 and press Enter.

    The white stripe moves to the left. With a cell, color stops are sorted by the position within the gradient. With a shape, gradient stops are sorted in the order that you add them to the collection.

    image from book

    The gradient in the rectangle now looks very similar to the gradient in the cell, but because it is a shape, you can completely change its look.

  9. Type myShape.AutoShapeType = msoShapeRightArrow and press Enter to change the rectangle into a right-pointing arrow.

    image from book

There are almost 200 possible shape types to choose from! You can use shapes on your worksheet for anything from adding interest, to focusing attention on a cell value, to providing snazzy buttons to run macros, to creating a sophisticated company logo.

Tip 

When you create a new shape on the worksheet, Excel gives it a default name-usually something like Rectangle 2 or Oval 5. You can see the name of the shape in the Name Box to the left of the Formula Bar. You can also use the Name Box to type a new name for the shape. When you refer to the shape from a macro, you can make your macros easier to read and less likely to contain errors if you change the shape names to something meaningful.

Reference a Selected Shape

The earliest versions of Excel did not have shapes. Instead, they had something called drawing objects. Shapes were developed as graphical objects that could be used across all Microsoft Office applications, and they are much more powerful than the old drawing objects. Shapes in the 2007 release of Microsoft Office are even more powerful than in earlier versions.

Drawing objects had their own extensive set of objects. To maintain backward compatibility, Excel didn’t remove the old DrawingObjects; they’re still there, but they’re hidden. Occasionally, you might see vestiges of these old drawing objects. One place that you encounter them is when working with the Selection property to refer to the currenly selected object. When you’re comfortable with how to assign the currently selected object to a Shape object variable, you don’t have to think much about the old drawing objects.

  1. In Excel, select a shape-the shape you created in the previous section will do fine. Then create the following macro shell in Visual Basic, and press F8 twice to initialize the variable.

    Sub SelectionShape()   Dim s As Shape   End Sub
  2. In the Immediate window, type Set s = Selection and press Enter.

    You get an error message that says “Type mismatch.” When you created the object, you assigned it to a Shape variable, so why can’t you assign it to a similar variable now?

  3. Type ?TypeName(Selection) and press Enter.

    The word Rectangle appears. (All the AutoShapeTypes-with the exception of a few of the basic shape types-belong to the Rectangle object class.) However, if you search for a Rectangle class in the Object Browser, you won’t find one. If you place the insertion point in the word Rectangle and press F1, you get a generic message, but nothing relevant to a Rectangle.

  4. Place the insertion point in the word Shape (in the variable declaration), and press F1. When prompted for clarification, leave Shape (Object) selected, and click Help.

    Troubleshooting 

    If you’re not connected to the Internet, you can still access help on your computer. In Excel Help, click the Search button arrow and click Developer Reference at the bottom of the list (in the Content From This Computer section).

    The Help topic explains that if you want to work with shapes within the selection, you should use a ShapeRange collection.

    image from book

  5. Scroll to the bottom of the topic, and click the link to the ShapeRange Object Help topic. Scroll to the bottom to the Returning All or Some of the Selected Shapes on a Document subheading.

    This section gives an example of using ShapeRange with the selection. The ActiveWindow object at the beginning has no effect.

    image from book

    The explanation says to use the ShapeRange property of the Selection object. But there is no such thing as a Selection object. Selection is a property that returns whatever kind of object happens to be currently selected. The Help topic explanation is simply masking over the existence of the Rectangle object.

  6. In Visual Basic, press F2 to display the Object Browser. Right-click in the list of classes, and click Show Hidden Members. Then select the Rectangle class on the left and the ShapeRange property on the right.

    image from book

  7. In the Immediate window, type Set s = Selection.ShapeRange(1) and press Enter.

    This is essentially the same expression as is in the Help topic, and it does work. If you pretend that Selection is an object, as the Help topic suggests, then you can think of ShapeRange as a property of that imaginary object.

  8. Type s.AutoShapeType = msoShapePentagon and press Enter to confirm that the selected shape really is properly assigned to the variable.

    image from book

  9. Press F5 to stop the macro.

The only reason you need to know about the hidden objects is that selecting an object in the user interface and then typing ?TypeName(Selection) in the Immediate window is a very useful way to find out the appropriate object class for a variable. Knowing about the hidden objects can also make the result less confusing.

image from book
Shape-Related Object Classes

The three (visible) object classes that relate to shapes are easily confused.

The Shapes object is a collection of all the Shape objects on a worksheet. To refer to the Shapes object, you use the Shapes property of a worksheet. As with most collections, you can specify a single item from the collection by using the name or number of the item. A single item from the Shapes collection is a Shape (singular) object.

A Shape object is the primary mechanism of interacting with a shape on a work-sheet. You can move it, resize it, name it, apply formatting to it, and even delete it.

A ShapeRange object is like a Shape object, except that a shape range can include more than one shape. In other words, it is a collection. To create a shape range from a Shapes object, you use the Range property, combined with the Array function from Visual Basic. (This is the same syntax as selecting multiple worksheets by using an Array function combined with the Worksheets property.) The Array function turns a list of items into the single argument required by the Range property.

See Also 

For information about using the Array function with worksheets, see the section titled “Manipulate Multiple Worksheets” in Chapter 3, “Explore Workbooks and Worksheets.”

When you want to modify multiple shapes, use a shape range. When you want to modify a single shape, it’s easier to just use a shape, even though a shape range can consist of only one shape. A few of the properties available for a single shape don’t work with a shape range. For example, it doesn’t make sense to give a name to an arbitrary set of shapes, so the Name property returns an error message if you use it with a shape range that contains more than one shape.

Shapes allow you to create extremely powerful graphical effects. But keeping the different types of objects straight can be a bit confusing at first. Here’s a summary:

  • Shapes collection object. Use the Shapes collection object for selecting all the shapes on a worksheet, for accesing a single shape, or for adding a new shape. You can apply only limited formatting to the entire Shapes collection.

  • Shape object. Use the Shape object for manipulating a single shape.

  • ShapeRange collection object. Use the ShapeRange object for manipulating multiple shapes at the same time.

image from book

Use an AutoShape to Create a Logo

Excel has a very large collection of shapes that you can insert into a worksheet. But what makes them remarkable is the degree to which you can enhance and modify the shapes. You can even add text to the shapes and format the text with the same flexibility as the shape itself.

Tip 

The MakeLogo macro is available in the MakeLogo.txt file in the folder with practice files, so you can copy it into a module and step through it if you want. But Auto Lists makes typing the statements relatively easy and will help you understand how the objects work. At any time while executing the statements, you can switch to the user interface and look at the corresponding properties there.

  1. In Excel, activate the Logo worksheet-which is blank. Then in Visual Basic, create the following macro shell, and press F8 twice to initialize the variables.

    Sub MakeLogo()   Dim s As Shape   Dim tf As TextFrame2   Dim tr As TextRange2   Dim sf As ShadowFormat   End Sub 

    Some of the object classes may seem unfamiliar, but they are simply subobjects for detailed parts of the shape’s format. The reason TextFrame2 and TextRange2 have a 2 at the end is because these are new, improved versions of previously existing objects. Old macros that access the (now hidden) TextFrame or TextRange objects will continue to run, but new macros can take advantage of the new capabilities.

  2. Enter the following statements into the Immediate window.

    Set s = Main.Shapes.AddShape(msoShapeUpArrowCallout, 0, 0, 72, 72) s.Select Set tf = s.TextFrame2  Set tr = tf.TextRange  Set sf = tr.Font.Shadow s.Name = "Logo"

    image from book

    The first statement creates a new shape, similar to the way you created a new rectangle earlier in this chapter. The size and location are unimportant because you will align them to cell boundaries in separate statements later. The Select statement simply makes it easier to see the shape you’re modifying; you don’t need it in the finished macro. The other statements assign references to sub-objects that pertain to formatting. The final statement simply gives a name to the object in case you ever want to refer to it in a different macro.

  3. Enter the following statements into the Immediate window.

    s.Left = Range("C6").Left s.Top = Range("C6").Top s.Width = Range("C:F").Width s.Height = Range("6:15").Height 

    These statements show how easy it is to align a shape with the worksheet grid. Note that the Left, Top, Width, and Height properties of a range are all read-only- you have to use different properties or methods to change their values-but they are very helpful in aligning shapes.

    image from book

    You now have a basic shape on the worksheet. Before you continue, you may want to explore the shape in Excel. The shape has four yellow triangles called adjustment handles. These allow you to adjust the relative size of different portions of the shape, effectively turning the hundreds of available shapes into millions. If you move the adjustment handles in Excel, you’ll understand better what the following macro statements are doing.

  4. Enter the following statements into the Immediate window.

    s.Adjustments(1) = 1.2 s.Adjustments(2) = 0.6 s.Adjustments(3) = 0.15 s.Adjustments(4) = 0.7 

    image from book

    An Up Arrow Callout shape has four adjustment handles: the first adjusts the width of the arrow shaft, the second adjusts the width of the arrow head, the third adjusts the height of the arrow head, and the fourth adjusts the height of the main rectangular body. Different shapes have different adjustments. The best way to determine the appropriate values is simply to experiment.

  5. In Excel, right-click the shape, and click Format Shape. Then enter the following statements in the Immediate window.

    s.Line.Visible = msoFalse s.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2 s.Fill.ForeColor.TintAndShade = -0.3 s.Fill.Transparency = 0.25 

    image from book

    These statements remove the border and change the fill color. Transparency greater than 0 allows the worksheet grid to show through the shape. The Format Shape dialog box can stay visible as you execute macro statements, so you can immediately see the effect of the statement.

  6. Enter the following statements into the Immediate window.

    tr = "Lucerne" & vbCrLf & "Publishing" tr.Font.Size = 28 tr.Font.Bold = msoTrue 

    These statements add the company name to the shape and make the name large and bold. vbCrLf is a special constant in Visual Basic that means Carriage Return Line Feed which was the description of a new line when Visual Basic was first invented. The ampersand characters join parts of the name together, allowing you to insert the new line into the middle of the name.

  7. In Excel, right-click in the middle of the text, and click Format Text Effects. Then enter the following statements into the Immediate window.

    tr.Font.Spacing = 2 tf.VerticalAnchor = msoAnchorBottom tf.MarginBottom = 20 

    image from book

    Font spacing is part of the Font dialog box, which can’t stay open while you’re executing macro statements. The default spacing value is 0, which is neither expanded nor condensed. Alignment appears in the Format Text Effects dialog box, which can stay open as you execute statements. To center text in an irregular shape, the best approach is to pick an appropriate side to anchor the text to and then adjust the margin as needed to get a centered look. In this case, the bottom is a good reference point. In the macro, you use points to specify sizes, but the dialog box displays sizes in inches.

  8. In Excel, click the Text Outline page of the Format Text Effects dialog box. Then enter the following statements into the Immediate window.

    tr.Font.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2 tr.Font.Fill.ForeColor.TintAndShade = -0.6 tr.Font.Line.Visible = msoTrue tr.Font.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2 tr.Font.Line.ForeColor.TintAndShade = 0.3 

    image from book

    These statements give you a taste of the kind of formatting you can apply to a font-separately from the container shape. Because the Font Format object is separate from the Shape Format object, it can use the same Fill and Line properties as the shape itself. As a result, you can apply most of the things you learned about formatting the shape to formatting the font.

  9. In Excel, click the Shadow page of the Format Text Effects dialog box. Then enter the following statements into the Immediate window.

    sf.Style = msoShadowStyleOuterShadow sf.ForeColor.ObjectThemeColor = msoThemeColorLight1 sf.OffsetX = 5 sf.OffsetY = 5 sf.Blur = 8 

    image from book

    These statements show you what you can do with a font shadow. You need to create the shadow, then give it a color, and then shift it horizontally and vertically. Adding a blur effect makes the shadow look more natural.

  10. In Excel, right-click the shape, click Format Shape, and click 3-D Format. Then enter the following statements into the Immediate window.

    s.ThreeD.BevelTopDepth = 12 s.ThreeD.BevelTopInset = 24 s.ThreeD.BevelTopType = msoBevelSoftRound s.ThreeD.PresetMaterial = msoMaterialMetal2 s.ThreeD.PresetLighting = msoLightRigFlood 

    image from book

    These statements give you a taste of 3-D formatting. Even without using the macro recorder, by experimenting with formatting in the user interface, you can see the possibilities. You can apply 3-D formatting either to the shape (the Format Shape dialog box) or to the text (the Format Text Effects dialog box). After you add 3-D effects to a shape, you can add lighting and transparency options that, just a few years ago, were available only on specialized graphics devices.

  11. In Excel, click the 3-D Rotation page of the Format Shape dialog box, type s.ThreeD.RotationX = 40 in the Immediate window, and then press Enter to see the look of the logo with a different 3-D orientation.

    You can also rotate the shape around Y or Z axes.

    image from book

  12. Press F5 to stop the macro. Then copy the contents of the Immediate window into the macro, replace Sheet3 with ActiveSheet, create a new worksheet in Excel, and test the macro.

This logo should give you a good sense of the range of formatting that you can applyto a shape-and how to create the statements in a macro. The properties in the object model correspond very closely to the options in the user interface. You may even discover properties in the object model that help you learn about options you hadn’t noticed in the user interface.

See Also 

The MakeLogo macro was used to create the animated logo used in the Enterprise Information System (EIS) application included in the Appendix.

Use Grouped Shapes to Create Macro Buttons

Multiple shapes can be combined into a single group. For example, on the Map page of the Chapter06 workbook, there is a map that shows the states in the western United States where Lucerne Publishing sells products.

image from book

The map consists of a group of shapes-one for each state. From a macro, you can refer to each individual component of the group. You can even make each state into a button that runs a macro.

Tip 

The MakeMapButtons macro is available in the same folder as MakeMap.txt, so you could copy it into a module and step through it if you want, but Auto Lists makes typing the statements relatively easy and will help you understand how the objects work. This macro was used to help create the user interface in the Enterprise Information System (EIS) application in the Appendix.

  1. In Excel, drag the Map worksheet tab to the right, and as you release the mouse, press the Ctrl key to create a copy of the worksheet.

  2. In Visual Basic, create the following placeholder macro.

    Sub StateButton()   MsgBox "Hello" End Sub 

    You’ll make a state shape run this macro.

  3. Create the following macro shell, and press F8 twice to initialize the variable.

    Sub MakeMapButtons()   Dim s As Shape   End Sub
  4. Enter the following statements into the Immediate window.

    myNumber = 4 myName = "Washington" myCaption = "WA" myColor = 6 

    These attributes are unique for the state of Washington. Washington is the fourth item in the group. (Trial-and-error is the best approach to determine the order of a shape within a group.) Once you’ve written the macro, you can change these values and run the same macro to apply the appropriate formatting to a different state.

  5. Enter the following statements into the Immediate window.

    Set s = ActiveSheet.Shapes(1).GroupItems(myNumber) s.Select s.Name = myName 

    The map of Washington state is selected, and the name in the Name Box changes from the default name to Washington.

    image from book

    A Shape object has a GroupItems property that returns a collection of all the shapes in the group. You can use a number to reference a single shape from the collection. The grouped item is not part of the worksheet’s Shapes collection, but it is part of the GroupItems collection for the shape that forms the group. The Select statement enables you to see which state you are referencing. You can leave that out of the final macro. There is no requirement to assign a name to the individual shape, but if you do, it will make it easier to reference the state from a different macro later. If you do change the name, you can see it in the Name Box in Excel.

  6. Enter the following statements into the Immediate window.

    s.Fill.ForeColor.ObjectThemeColor = myColor s.ThreeD.BevelTopDepth = 6 

    Washington state changes color and takes on a three-dimensional look-like a button.

    image from book

    You can change the color and 3-D properties of a grouped shape, just as you can a non-grouped shape. Shapes have a ThreeD property that allows you to access a large number of three-dimensional formatting effects, including BevelTopDepth. Setting the BevelTopDepth of the shape makes it look more like a button.

  7. Enter the following statements into the Immediate window.

    s.TextFrame2.TextRange = myCaption s.TextFrame2.HorizontalAnchor = msoAnchorCenter s.TextFrame2.VerticalAnchor = msoAnchorMiddle

    image from book

    When you first add text to a shape, the text is attached to the top-left corner. The HorizontalAnchor and VerticalAnchor properties allow you to center the text within the shape. The Washington shape is basically rectangular, so centering the text works fine. For some states, you may need to manually adjust the location of the text so that it appears centered.

  8. Enter the following statements into the Immediate window.

    s.TextFrame2.TextRange.Font.Size = 20 s.TextFrame2.TextRange.Font.Bold = msoTrue s.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor = _   msoThemeColorLight1 s.Fill.OneColorGradient msoGradientHorizontal, 1, 0 s.TextFrame2.TextRange.Font.Reflection.Type = msoReflectionType5 

    The first three statements simply make the text easier to read. Adding a gradient to the shape and a reflection to the text gives the button a classy effect.

    image from book

    A shape has an OnAction property that allows you to link the shape to a macro. In principle, you should be able to assign the OnAction property to any shape- including one that is part of a group. But in fact, you can only use the OnAction property of a top-level shape. Fortunately, it is easy to ungroup and regroup the shapes.

  9. Enter the following statements into the Immediate window.

    s.Parent.Parent.Ungroup s.OnAction = "StateButton" s.DrawingObject.ShapeRange.Regroup 

    The Ungroup property of a shape works only if the shape contains grouped items. The Parent of the individual grouped shape is not a shape, but is a GroupShapes object. The parent of the grouped shapes is the shape you really need. There is no “grandparent” property, so you simply use the Parent property twice. Regroup isa method of a shape range, not of a shape-although it considers only the first item in the collection when deciding what to regroup. A shape does not have a ShapeRange property. But to get from a shape-such as the current Washington state shape-to its shape range, you can take a shortcut through the hidden DrawingObject property.

    Tip 

    If you don’t want to use the hidden DrawingObject property and you have given a name to the shape, you can access the shape range from the Shapes collection. For example, to regroup a shape named Washington, you can use the statement ActiveSheet.Shapes.Range("Washington").Regroup.

  10. Press F5 to stop the MakeMapButtons macro, and then copy the statements from the Immediate window into it.

  11. Delete the statement that selects the shape from the macro, and then select any cell on the worksheet (to deselect the map). Change the constants at the top to the following, and press F5 to test the macro.

    myNumber = 3  myName = "Oregon"  myCaption = "OR"  myColor = 9 

    The macro should format the shape for Oregon without changing the selection.

    See Also 

    In the section titled “Creating Custom Functions” in Chapter 8, “Extend Excel and Visual Basic,” you will learn how to create and use arguments for macros that you write, allowing you to make them even more reusable.

  12. In Excel, try clicking the formatted states.

    You should see the placeholder message.

    image from book

    Grouped shapes are almost as easy to work with as simple shapes. Assigning a macro to the OnAction property is one of the few reasons you need to explicitly ungroup a shape before you can use it. Notice also that by using explicit variables at the top of a macro to store values that can vary, you can make the macro easier to reuse: you just change the variable values all in one place.

    See Also 

    The MakeMapButtons macro was used to create the map interface for the Enterprise Information System (EIS) application included in the Appendix.

image from book
Selecting Multiple Items

Grouped shapes can be very convenient to work with from a macro. To create your own group in the user interface, you need to select multiple items. One way to select multiple items is to hold down the Ctrl key as you click. Another is to drag a rectangle around the objects. But when you drag a rectangle on a worksheet, you simply select a range of cells.

Excel has a special mode that allows you to drag a rectangle to select multiple objects. On the Home tab of the Ribbon, in the Editing Group, click the Find & Select arrow, and then click Select Objects. While Select Objects is turned on, clicking or dragging on the worksheet does not select cells.

If you forget to turn off Select Objects, the behavior of Excel can be disconcerting. If you use Select Objects frequently, you may want to add the button to the Quick Access Toolbar so that you always see the current state and can turn it on and off easily.

image from book



Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net