More Tricks with Graphic Objects

But wait, there's more! We'll now describe a few features that are hard to classify with the other graphics features. You can essentially turn any graphic object into a button by assigning a macro to it. In addition, you can take pictures of your worksheets and use those pictures in Excel workbooks (or even in other programs); they can appear as static bitmaps or dynamic windows to display what's happening in other areas of the workbook or in other workbooks.

Assigning Macros to Objects

You can attach a macro to any object, allowing you to activate the macro by simply clicking the object. To attach a macro to an object, do the following:

1. Right-click the object, and click the Assign Macro command.

2. When the Assign Macro dialog box appears (shown in Figure 10-47), assign a macro to the object by clicking New to create a new macro using the Visual Basic Editor, by clicking Record to create a new macro by example, or by selecting an existing macro from the list.

Figure 10-47: Assigning macros to objects turns them into buttons.

For more information about macros, see Chapter 26, "Recording Macros."

Inside Out-Grouped Objects and Macros

Assigning macros to objects is a cool way to create some crazy-looking "buttons." You can also assign macros to grouped objects, or to individual objects that you subsequently group, but you can't do both. For example, say you assign macros to two objects and then group the objects. You can click each object to run each macro, just as if they were ungrouped. But if you assign another macro to the grouped object, the new macro overrides the existing macros.

Taking Pictures of Your Worksheets

Excel provides techniques for taking pictures of your worksheets: You can use the Copy As Picture, Paste As Picture, and Paste Picture Link commands on the Paste menu on the Home tab, and you can use the Camera button, which you can add to the Quick Access Toolbar.

Using the Camera Button

With the Camera button, you can copy an image of a range of cells and paste the image anywhere on the same worksheet, on another worksheet in the same workbook, or in another workbook. Copying an image isn't the same as copying the cells using the Copy button. When you use the Camera button, you copy a linked image of selected cells, not their contents. As a result, the image changes dynamically if the contents of the original cells change.

Note 

To add the Camera button to a toolbar, click the Microsoft Office Button, Excel Options, Customize. In the Choose Commands From drop-down list, select All Commands, select Camera in the list, and then click the Add button.

Figure 10-48 shows two worksheets side by side. If you select the range G3:G16 in the Pacific Sales Summary worksheet on the left and click the Camera button, the pointer changes from a plus sign to a crosshairs. Click anywhere in Book2 to select it, and then use the crosshairs pointer to click where you want the upper-left corner of the picture to appear. Excel embeds the picture as shown on the right in Figure 10-48.

Figure 10-48: The Camera button creates a linked image of a selected range.

Note 

Any graphic objects that happen to be within or overlapping the selected range also display in the linked picture.

After you paste the picture, you can change its size and proportions by dragging its selection handles and treating it just like any other graphic object. Changes in shape, size, and formatting do not affect the dynamic updating of the data displayed in the picture.

If you select the embedded picture, the formula bar displays a formula much like any other cell-linking formula, as shown in Figure 10-48. After you create the picture, you can edit the formula in the formula bar, and the picture changes accordingly. You can even change the reference formula to link a completely different worksheet or workbook. The link between the source and destination documents has another distinctive and useful characteristic. Suppose you close the Pacific Sales worksheet in Figure 10-48. If you then double-click the embedded image in Book2, Pacific Sales opens automatically, with the pictured range selected.

For more information about linking formulas, see "Formula Fundamentals" on page 427.

Using the Copy As Picture and Paste As Picture Commands

The Copy As Picture command rather unintuitively lives on the As Picture menu that you can find by clicking the Paste menu on the Home tab. It creates an image of a selected area of the worksheet just as the Camera button does, but with an important difference. The copied picture is static, with no links to any worksheet. Static pictures are useful when you don't need to update data or when the speed with which Excel recalculates the worksheet is more important. You can use the Copy As Picture command to add images of worksheets and charts to reports or other documents via the Clipboard. After you take the picture, you can paste it in another Excel document or in a document from any program that accepts Clipboard images.

Before clicking the Copy As Picture command, select the cells, object, or chart you want to copy. When you click the command, the dialog box in Figure 10-49 appears.

Figure 10-49: Use the Copy As Picture command to create "screen shots" of selected areas of your worksheets.

The options in the Copy Picture dialog box are as follows:

• As Shown On Screen Reproduces the selection at the moment you take the picture.

• As Shown When Printed Reproduces the selection according to the settings in the Page Setup dialog box that control the printing of gridlines and row and column headings.

• Picture Copies the picture in a format that can be displayed on monitors with different resolutions. This is useful if the picture will be viewed on different computers.

• Bitmap Copies the picture in a format that is correct only when the display resolution is the same as the screen from which it was copied.

After you copy an image to the Clipboard, you can paste the image anywhere you want-in another location on the worksheet, on another worksheet, or even in a document from another program. The Paste As Picture command is similar to Copy As Picture, except you don't see the Copy Picture dialog box. Instead, the image is pasted using the default As Shown When Printed option as the default format.

The Paste Picture Link command creates a linked image just like the Camera button does, except that the resulting image also uses the As Shown When Printed format instead of the As Shown On Screen option, as the Camera button does.