Using Auto Macros

Word, Excel, PowerPoint, and Outlook all enable you to create macros that will run when the applications start or quit, or when you open or close a specific document, workbook, or presentation. In all cases, the trick lies in putting the macro in the right place, and giving the macro the correct name. (In PowerPoint, you must clear a couple of additional hurdles.)

These so-called Auto macros can come in handy when you want to modify the application itself when it starts—to load the most recently used document, for example. This technique is also effective when you need to modify a document before the user starts working on it—to automatically calculate an invoice number, for example.

Using Auto Macros in Word

Word responds to a number of different Auto events, but we generally recommend you stick to one of these five events:

  • AutoExec() and AutoExit(), which run when Word starts and quits, respectively. These macros have to be placed in a module in Normal.dot.

  • Document_Open() and Document_Close(), which fire when the document containing the macros is opened or closed. If you store macros with either of these names in a Word template, they'll also run when you open or close documents based on the template.

    The Document_Close() macro runs before Word asks whether you want to save changes.

  • Document_New(), which fires when you create a new document based on the template (or document) containing the name.

Although you might place AutoExec and AutoExit macros in any module in Normal.dot, Document_Open(), Document_Close(), and Document_New() must all be created in a special area called Microsoft Word Objects.

Say your boss has taken a stance against the paperless office and decreed that you need to print an additional hard copy of every memo and deliver it to the filing clerk. You can easily create a macro that reminds people of the new requirement, and attach it to your company's memo template. Here's how:

  1. In Word, open your company's memo template. In this example, Memo.dot was chosen.

  2. Start VBA/Word by choosing Tools, Macro, Visual Basic Editor (or by pressing Alt+F11).

→ For details on the VBE, see "Using the VBA Editor".

  1. In the Project Explorer, navigate to the Memo project, called TemplateProject (Memo). Then double-click Microsoft Word Objects, and double-click ThisDocument.

    In Word, projects attached to templates are called TemplateProject(Template name). So, for example, the project attached to Letterhead.dot is called TemplateProject(Letterhead).

  2. To have the warning message appear whenever a user creates a new memo based on this template, click the Object drop-down list and choose Document. Then, in the Procedure drop-down list, choose New. VBA/Word provides the Sub Document_New()/End Sub pair (see Figure 40.7).

    Figure 40.7. The Document_Open(), Document_Close(), and Document_New() Auto macros go in the project's Microsoft Word Objects folder.

  3. Type this one line between Sub and End Sub:

    MsgBox "Remember to make an extra copy for the filing clerk."

That's all there is to it. (Surprisingly, you don't even need to save your changes.) To see the macro in action, create a new document based on Memo.dot. The reminder appears before the user can even start typing.

Using Auto Macros in Excel

Although Word handles just three document-oriented Auto events, Excel keeps track of 20 events—Before Close, Before Save, New Sheet, Sheet Activate, and many more—and you can write pieces of code to be invoked whenever one of these 20 events occurs. To see a complete list of these events and a discussion of how to write code for them, consult the online Help topic "Workbook Object Events."

It's relatively easy to follow the instructions in online Help to create Auto macros that correspond to most workbook events, but it's not at all obvious how to create Auto macros that run when Excel starts or exits. For example, let's assume that you want to maximize the Excel window every time Excel starts. Follow these steps:

  1. Start Excel and press Alt+F11 to open the VBA Editor. Look in the Project Explorer for a VBAProject(Personal.xls) entry.

If you can't locate your Personal.xls file, see "Missing Personal.xls" in the "Troubleshooting" section at the end of this chapter.

  1. Double-click Personal.xls and choose Modules, Module1. That puts you in Module1 of your Personal Macro Workbook.

  2. Choose Insert, Procedure to bring up Excel's Add Procedure dialog box (see Figure 40.8). Type Auto_Open (note the required underscore character), and click OK.

    Figure 40.8. An Auto_Open() sub routine in Excel's Personal Macro Work book (Personal.xls) will run every time Excel starts.

  3. In the newly created Auto_Open() subroutine, type the one-line program you see in Figure 40.9.

    Figure 40.9. This one-line program maximizes the Excel application window.

  4. Close Excel. When asked whether you want to save changes to your personal workbook, click Yes.

Every time you start Excel, the Auto_Open() macro in Personal.xls kicks in and maximizes Excel.

Using Auto Macros in PowerPoint

PowerPoint, by contrast, requires that you use class modules to house macros that react to the application's Auto events. Then you have to run a separate program that activates those events before the events will "fire." The procedure is complicated. For details, look in the PowerPoint Help topic "Application Events."

Using Auto Macros in Outlook

Outlook makes it easy to run macros automatically when the application starts and ends, as long as you have your macro security setting at Medium or Low (Tools, Macro, Security). Say you want to have Outlook put a message on the screen every time you start it that says, "Remember to back up!" Here's how:

  1. Start Outlook. Choose Tools, Macro, and then pick Visual Basic Editor (or press Alt+F11) to bring up the VBA/Outlook editor.

  2. In the Project Explorer window, navigate to Project1 Microsoft Outlook Objects/ThisOutlookSession. Double-click ThisOutlookSession.

  3. In the Object drop-down list, choose Application. In the Procedure drop-down list, choose Startup. VBA/Outlook responds by creating the procedure pair:

    Private Sub Application_Startup()
End Sub

  4. Complete the following VBA/Outlook program. Exit Outlook and tell it to save changes to ThisOutlookSession.

    Private Sub Application_Startup()
MsgBox "Remember to back up!"
End Sub

The next time you start Outlook, you'll receive this warning: "This OutlookSession contains macros. Macros might contain viruses. It is always safe to disable macros, but if the macros are legitimate, you might lose some functionality." If you respond by choosing Enable Macros, the backup message appears.

Using Auto Macros in Access

Access sports a similar AutoExec capability, but it's limited to old-fashioned macros—VBA need not apply. For more information, look for the Help topic "AutoExec."

Creating Toolbar Buttons, Menus, and Key Combinations

To make your macros more accessible, consider placing those you use most often on toolbars or menus. Office lets you place toolbar or menu entries on existing toolbars or menus. For example, you could put a toolbar button for a custom macro print routine right next to the Print button on the Standard toolbar, or you could stick it between Print Preview and Print on the File menu.

You can also create your own toolbars or top-level menus, and arrange buttons and menu items where you like. This approach is particularly useful if you have a substantial number of macros associated with a particular template: By keeping them off the built-in toolbars and menus, you lessen the potential for confusion.

Tip from

Give related custom menu items a uniform "look" by placing the same icon on the menu. Say you have a memo template with a dozen different macros, assigned to several different menus. If all of the menu items have the same "M" icon, users will be able to easily tell, visually, which menu items go with the memo template.


→ For details on making customized toolbars, menus, and keyboard shortcuts appear, see "Customizing Toolbars," "Customizing Built-In Menus," and "Bypassing Menus with Keyboard Shortcuts".

Surprisingly, the mechanics for assigning a macro to a menu item or toolbar button differs slightly among the Office applications. In Word, PowerPoint, and Outlook, for example, you can drag a macro straight from the macro list (choose Tools, Customize, Commands) directly onto a menu or toolbar. In Excel and FrontPage, however, you have to drag a placeholder onto the menu or toolbar, and then right-click to select the macro. The net result is the same.

Substituting for Built-In Commands

Word—and Word alone—allows you to take over the built-in commands used by the application itself. You can preempt every single one of more than 1,000 commands, from AcceptAllChangesInDoc to WordUnderline. The macro you write will run instead of the built-in Word command, and it doesn't matter how you invoke the built-in command: by clicking a menu choice or toolbar button, pressing a key combination, or even using external means (for example, via Object Linking and Embedding from Visual Basic). Your macro takes precedence.

Where you place the macro controls the extent of its influence. For example

  • If you place a macro called FilePrint in a document, it will run whenever you (or an other user) print that document. You might use such a macro on a corporate network to keep a running list of who has printed the document, and when.

  • If you place a macro called ToolsWordCount in a template, your ToolsWordCount macro will run whenever someone runs a word count in a document based on that template. You might want to attach such a macro to a catalog product description template and have it plug the numbers into a table at the top.

  • If you place a ViewZoom macro in the Normal.dot project—the global template—your macro will run in place of the standard View Zoom (for example, by choosing View, Zoom) all the time. Such a macro might prompt you and ask whether you want to use your favorite zoom factor.

The hardest part of writing macros to supplant built-in Word commands lies in figuring out the name of the macro: FilePrint and ViewZoom are pretty obvious, but ToolsWordCount isn't. There are two undocumented tricks that can help:

  • To see which built-in command sits behind a Word menu item or toolbar button, press and hold the Ctrl and Alt keys simultaneously and press the "plus" sign on the numeric keypad. The mouse pointer turns into a shape that resembles a cloverleaf. Click the menu item or toolbar button in question to display the Customize Keyboard dialog box, with the correct command name highlighted. In Figure 40.10, for example, Ctrl+Alt+NumPad Plus (+) was pressed, and Tools, Word Count was clicked.

    Figure 40.10. To find the built-in command behind a menu item or toolbar button, press Ctrl+Alt+NumPad Plus and click the item.

  • To display a complete list of built-in Word commands, choose Tools, Macro, Macros; then choose Word Commands from the Macros In drop-down list.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.70.255