Controlling How VBA Applications Start
There are countless ways to run a macro. The most direct way, of course, is to choose from the pull-down menus. But there are far more productive ways to automate work processes, especially when you're creating a document, workbook, or presentation that you want to pass along to other users.
You can create a macro that runs automatically every time a user opens a specific Word document or Excel workbook, for example. Excel keeps track of 20 workbook-related events (Before Print, Before Save, when a Sheet is Activated), and you can write macros that run when any of those events "fire." Use these Auto macros to ask the user for initial information for a report, or to update a worksheet with up-to-the-minute data off the Web every time a user opens a workbook.
You can also set up "hot" areas within a document, worksheet, or PowerPoint slide, so clicking a bit of text or graphics will run a macro. PowerPoint lets you run a macro by passing the mouse pointer over a "hot" spot on a slide.
In Outlook, you can specify that a macro be run when the application starts or quits, new mail arrives, a message gets sent, or a Reminder shows up onscreen.
FrontPage and Publisher have the least developed macro-triggering capabilities of all the Office applications. Without a lot of work, the most you can do is put a macro on the menu or a toolbar.
At the high end, you can even replace Word's built-in functions with macros of your own devising. For example, you can create your own File Open routine to run in place of Word's File Open. That solution might come in handy if your company uses nonstandard file extensions and you want to see a list of those files every time you choose File, Open.
"Hot" Linking to Macros
Word, Excel, and PowerPoint include their own variations on a macro hot link—a picture or piece of text that runs a macro when clicked. Surprisingly, however, the technique you use to implement these hot links varies widely from application to application.
Hot Linking in Word
In Word, you can implement hot links with MacroButton fields.
→ For details on Word fields in general, see "Using Fields Intelligently".
→ To learn more about building your own interactive VBA programs, see "Building Interactive VBA Programs".
Say you want to add the sentence "Double-click HERE to run Counter," so that double-clicking the word HERE will run the macro called Counter, which was created in the preceding chapter:
In a Word document, type the sentence "Double-click to run Counter" (without quotes). Don't include the word HERE yet.
Position the insertion point where you want to add the hot word HERE.
Choose Insert, Field. You'll see the Field dialog box shown in Figure 40.1. In the Categories box, select Document Automation; and then select MacroButton from the Field Names box.
Figure 40.1. When the user double-clicks a MacroButton field, the indicated macro runs.
In the Display Text field type the word HERE. Then in the Macro Name field select Counter and click OK.
After returning to the document, verify that the macro runs every time you double-click the hot text.
Word doesn't limit you to text in a MacroButton field; you can use pictures as well. After you have the field set up with text using the preceding steps, it's easy to convert to a picture. Use this two-step method to make a picture in Word "hot" (first, create a MacroButton field with text, and then replace the text with a picture). It's much less error-prone than trying to type in the field from scratch.
With the sentence Double-click HERE to run Counter showing, choose Tools, Options. Click the View tab, select the Field Codes check box, and click OK. You should be able to see the MacroButton field (see Figure 40.2).
Figure 40.2. The MacroButton field enables the "hot spot" in this Word document.
To replace the hot text with a picture, choose Insert, Picture, Clip Art, and choose a picture (see Figure 40.3).
Figure 40.3. You can use a picture for the "hot spot"—in this case, a photograph—by replacing the text in the MacroButton field with a picture.
Tip from 
Any of the Insert Picture options will work—From File, From Scanner, and so on.
Choose Tools, Options; on the View tab, clear the Field Codes check box. Click OK, and your document returns to showing the results of field codes, instead of the codes themselves.
In Word, you can also run macros on entry or exit to a data-entry form field. When the user fills out a data-entry form and uses the Tab key to move into or out of a field defined on the form, you can have a specific macro run.
→ For details on Word data-entry forms, see "Creating a Data Entry Form".
In summary, in all situations except data-entry forms, Word documents can have hot text or pictures. It always takes a double-click to activate the hot link.
Hot Linking in Excel
Excel, surprisingly, has two entirely different ways to make text and pictures "hot":
You can assign a macro to a control on a data-entry form. The controls come from the Form toolbar. When you place a command button on an Excel data-entry form, Excel immediately prompts you to assign a macro to it. To make other controls "hot," right-click and select Assign Macro.
You can also assign a macro to a picture in the drawing layer.
→ For the definitive guide to drawing in Office documents, see "Working with the Drawing Layer".
Working with the Excel drawing layer gives you an enormous amount of flexibility in designating hot areas, because you can place drawings directly on top of cells, charts, or even other drawings. For example, let's assume you have a VBA/Excel macro called DistributeSheets that works with Outlook and creates email messages for everyone in your department, attaches the current worksheet to the messages, and mails all the messages. Use this technique to assign the macro DistributeSheets to the word Distribute in the Excel sentence, Distribute to department:
If the Drawing toolbar isn't visible, right-click any toolbar and choose Drawing from the list of available toolbars.
Click the Text Box icon, and then click and drag on the worksheet to add the text box. Type the text Distribute to department in the text box, as shown in Figure 40.4.
Figure 40.4. Use layered drawings to make Excel text hot. Start with a text box that includes the hot text.
Now draw a rectangle on top of the word Distribute—click the Rectangle icon, and draw the rectangle so that it completely covers the word Distribute (see Figure 40.5).
Figure 40.5. Then draw a rectangle that completely covers the hot text.
Right-click the rectangle and select Format AutoShape. In the Format AutoShape dialog box, choose No Fill in the Fill Color box, and No Line in the Line Color box. Click OK, and you'll be able to see the text Distribute through the now-transparent rectangle.
Right-click the rectangle once more and select Assign Macro. In the Assign Macro dialog box, select DistributeSheets (assuming you have a macro with that name) and click OK.
Hot Linking in PowerPoint
PowerPoint can also use the drawing layer to make text or drawings hot. Unlike Excel, however, you can make a PowerPoint macro run by merely passing your mouse over it.
Say you have a presentation with a slide that includes your company's current market capitalization (market capitalization = current stock price ´ number of shares outstanding). The PC you're using to make the presentation is connected to the Web, so you write a VBA/PowerPoint macro called GetMarketCap that retrieves your company's current stock price from the Web, calculates market capitalization, and puts that number in the slide.
Now you want to set up a hot drawing on the slide so that every time you pass your mouse pointer over the drawing, the market capitalization figure gets updated with up-to-the- second information from the Web. Here's how:
In PowerPoint, bring up the slide where you want to have a hot link.
Use the Drawing toolbar to create the picture you want to be hot. In Figure 40.6, an AutoShape has been drawn that looks like a lightning bolt.
Figure 40.6. To make a picture on a PowerPoint slide hot, start by drawing or inserting the picture on the slide.
Right-click the drawing and select Action Settings. In the Action Settings dialog box, you can choose from hyperlinks, programs, sounds, and other options.
→ For details about all these options, see "Advanced Navigation with Action Settings".
In summary, PowerPoint, too, can have hot pictures, but only in the drawing layer. The hot text trick works in PowerPoint, same as it does in Excel. But in PowerPoint, the hot link can work with either a single click, or by moving your mouse over the link.