The "Camera" tool
Excel's Camera tool (i.e., a toolbar button with an image of a camera) is a bit elusive these days. It no longer appears on any of the built-in toolbars. So if you want to use it, you'll need to add it to a toolbar (it's in the Tools category of the Customize dialog box). It's description is as follows:
Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.
Even if the Camera tool is not on a toolbar, you can get exactly the same effect by following these steps:
- Select the range
- Choose Edit - Copy
- Press the Shift key and choose Edit - Paste Picture Link (this command is available only when the Shift key is pressed).
- The result is a linked picture of the original range. You can move this picture anywhere you like.
The Camera tool is most often used to overcome Excel's problem with printing non-contiguous ranges. As you may know, when you try to print non-contiguous ranges, Excel insists on printing each area on a separate sheet of paper. You can use the Camera tool to create linked pictures of the ranges on a separate worksheet. You can then print the pictures on a single sheet of paper.
Another use for the Camera tool is to overlay a picture of a range on top of another range. In the figure below, some of the cells in columns C:F contain an "x" to indicate a missing value. Notice that column B shows a red [Enter] if any columns C:F contain an "x.". But where does the red [Enter] come from? Notice the formula bar is empty (the “[Empty]” text isn’t in the cell).
The red text actually comes from a linked picture of another range placed on top of the worksheet range. If the linked picture is formatted with no fill and no lines, it is essentially transparent, and the user can click right through it and select a cell. The only way to actually select the linked picture is to click on its (invisible) border.
Here's where it gets weird. This "click through a picture" technique no longer works in Excel 2000 or later. Clicking anywhere within the picture (even when formatted as no line and no fill) selects the picture, not the underlying cell.
But there's a solution. In order to be able to select “through” the picture, you need to save the workbook as an Excel 5.0/95 Workbook. Then reopen it, and you can select through the picture. You can even then re-save it as a normal workbook and you will still be able to select through the picture. Go figure... (contributed by Bob Umlas)
Finally, using a linked picture on an Excel 5/95 dialog sheet can be very useful. Unfortunately, it's not possible to use a linked picture on a UserForm.