Using VBA to Control Built-In Commands in the 2007 Office System

Summary: This article is a big picture introduction of common ways to control built-in commands in the 2007 Microsoft Office system using Microsoft Visual Basic for Applications (VBA). (8 printed pages)

Stephanie Krieger, arouet.net, Microsoft Office MVP

March 2009

Applies to: 2007 Microsoft Office system, Microsoft Office Access 2007, Microsoft Office Excel 2007, Microsoft Office Outlook 2007. Microsoft Office PowerPoint 2007, Microsoft Office Project 2007, Microsoft Office Publisher 2007, Microsoft Office SharePoint Designer 2007, Microsoft Office Visio 2007, and Microsoft Office Word 2007

Contents

• Office 2007 Is at Your Command!

• Intercept Built-in Word Commands

• Executing a Built-In Command

• Running Code Based on User Actions (a.k.a. Use Events)

• Controlling One Program from Another

• Time to Take Control

• Additional Resources

• About the Author

Office 2007 Is at Your Command!

Watch the Video: Using VBA to Control Built-In Commands in the 2007 Office System

When advanced Microsoft Office users begin to get comfortable with Microsoft Visual Basic for Applications (VBA), some of the most common requests that I hear are for ways to use VBA to override or interact with built-in commands and how to programmatically access one program from another.

If you think that these types of tasks require very advanced VBA knowledge, or that they are difficult or even impossible to accomplish, you’re far from alone. In fact, when I first started to learn VBA, I did not even know how to find help for these sorts of tasks because I didn’t know the right terminology to use.

Fortunately, however, like most things you can do with VBA, each of these tasks is easier to do than you might imagine. Once you check them out for yourself, you might even dare to call them fun.

So, to get you started, this article provides introductions and simple examples for several ways to interact with built-in functionality and put the Microsoft Office programs at your command.

Note
This article assumes that you have some previous experience writing VBA macros and that you are comfortable with VBA basics. For help filling in gaps or to get started with VBA, see Additional Resources.

Intercept Built-in Word Commands

Microsoft Office Word 2007 provides one of the easiest ways to interact with built-in commands. In the Macro dialog box that you can access from the View tab or the Developer tab, you can see a list of Word command names as shown in Figure 1. To use custom automation in place of a built-in Word command, all you need to do is use the Word command name as your macro name.

Figure 1. The Macros dialog box displaying built-in Word commands

For example, perhaps you have a template that is always used to create potentially sensitive content, such as new business proposals. You may want to intercept the print command in documents based on this template to check for tracked changes in the document before printing. To accomplish this, just add a macro named FilePrint in any VBA code module in that template, such as the example macro shown here.

Sub FilePrint()
Dim myQ As Integer
With ActiveDocument.Revisions
    If .Count > 0 Then
    myQ = MsgBox("Do you want to accept all tracked changes before printing?",vbQuestion + vbYesNo)
        If myQ = vbYes Then
          .AcceptAll
        Else 
          MsgBox “Please remember to resolve tracked changes before sharing this document electronically, to avoid sharing previously deleted content.”
        End If
    End If
End With
Dialogs(wdDialogFilePrint).Show
End Sub

The macro shown here takes the following steps:

1. Check for unresolved tracked revisions in the document.

2. If revisions are found, prompt the user with the option to accept those changes.

3. If the user clicks Yes in the message box, accept all tracked changes in the document.

4. If the user clicks No in the message box, display a warning.

5. Regardless of revisions found or user response, display the Print dialog box (which is what the built-in Print command does).

Although Word is the only program that exposes its built-in commands through VBA in exactly this fashion, you can interact with many actions in Word and all Office 2007 programs that support VBA using events. For more information, see Running Code Based on User Actions (a.k.a. Use Events).

Notes:

• Save time and reduce errors by letting Word start the macro for you. To do this, in the Macros dialog box, select the Word command you need. Then, in the Macros In list, select the name of the template in which you want to save the command and then click Create. VBA opens the Visual Basic Editor (VBE), adds the macro to an appropriate module, and includes the built-in functionality steps in the macro automatically.

• It’s important to note that most, but not all, of the Word commands listed in the Macros dialog box can be intercepted using this method.

• Remember that you can save the custom command in Normal.dotm or another global template to intercept the selected Word command regardless of the document in which you’re working.

Executing a Built-In Command

Word and Microsoft Office Excel 2007 both enable you to display built-in dialog boxes within your custom macros using the Application.Dialogs(name) statement. However, with the exception of file dialog boxes like Open and Save As, many of the 2007 release programs (such as Microsoft Office PowerPoint 2007) do not. Fortunately, it takes just one line of VBA code to display most built-in dialog boxes or execute many other built-in commands, as follows:

CommandBars.FindControl(Id:=IDnumber).Execute

Note that this method uses the CommandBars object which primarily refers to menus and toolbars that are no longer in use in the Ribbon-enabled 2007 release programs (although the Ribbon and Status Bar are members of the CommandBars collection). When you employ this method, you’re essentially duplicating the action of the user clicking the selected command bar control. However, even where the menus and toolbars are no longer in use, the associated command linkages still exist. (In fact, this method will work for many commands that are new to the 2007 release.)

For example, to display the Paste Special dialog box in PowerPoint, include the following line of code in your macro:

CommandBars.FindControl(Id:=755).Execute

Or, to display the Photo Album dialog box in PowerPoint (a command new in PowerPoint 2007), use the same statement with the ID 7063.

So, how do you find the ID number required to run the command you need? The answer depends on the program, as follows:

• For the 2007 release programs that support the new Fluent UI (a.k.a. the Ribbon), just download the 2007 Office System document: List of Control IDS from the Microsoft download center. This download is a zip folder containing Excel workbooks with lists of control IDs for PowerPoint, Word, Excel, Access, and Outlook. In those workbooks, you see a list of the IDs and supporting information used when adding any built-in command to a custom Ribbon tab. The last column in each of those workbooks lists the Policy ID number for each command, which is the ID number required in the preceding code sample.

• For programs that use the legacy (menu and toolbar) interface, you can write some VBA to create a list for yourself, or just use one line in the Immediate Window when searching for a specific ID. For example, in Microsoft Office Visio Professional 2007, the fifth control on the menu bar is the Format menu; the sixth control on the Format menu is the Theme command that opens the Theme task pane. To find the ID for the Theme command, use the following statement in the Immediate Window:

  ?Application.CommandBars("Menu Bar").Controls(5).Controls(6).ID
  

  If you want to confirm that you have the correct control, replace ID in the preceding statement with Caption to see the UI name for the control.

Note
Where commands are common to more than one program, the same ID is typically used in all programs that use the command. For example, the Paste Special ID shown earlier for PowerPoint also works in Word, Excel, Visio, Microsoft Office Publisher 2007, Microsoft Office Project 2007, Microsoft Office Access 2007, and Microsoft Office SharePoint Designer 2007.

Running Code Based on User Actions (a.k.a. Use Events)

Every 2007 release program that supports VBA provides a way to interact with a selection of actions, known as events. In most cases, these are actions based on a command selected by a user (such as opening a document or activating an application) or actions that result from something that a user does (such as an XML validation error in a Word document).

Similar to the option to override built-in Word commands as shown earlier, event-handling procedures enable you to add custom functionality when a specific action occurs. Unlike that previous approach, however, event handling does not take over the built-in action. Rather, the event (i.e., the built-in action) triggers your custom automation (sometimes called the event-handling procedure or simply the event handler).

Consider the following examples:

• You are editing a Word document with several other team members. You want to ensure that all editors take certain steps each time they open the document, such as by displaying a message box to which they have to respond in order to proceed with editing. Just add your message box in the Document_Open event.

• You work frequently with long Excel workbooks. Before you print any workbook, you want to make sure that the contents of each worksheet fit the width of the printed pages but that the text is still large enough to be readable. If you handle the Workbook_BeforePrint event, you can have Excel automatically check that each sheet is fit to one page wide but not scaled to less than a specified percentage.

• You have a CD label template in PowerPoint. To make the label easy to edit, you use a visible outline of a CD label on the slide. However, you don’t want the outline to be visible when you print the label (especially since it’s so easy for label sheets to get slightly misaligned in a printer). Using the PresentationPrint application event, you can hide those outline shapes automatically before the label prints.

Most of the 2007 release programs that support VBA (with the exception of PowerPoint and SharePoint Designer) provide a method by which you can easily access some events directly within a built-in document object. (These are commonly known as document-level events.) For example, when you look at the Project Explorer in the Word VBE, you see a ThisDocument object for each accessible VBA project. Similarly, in Excel, you see a ThisWorkbook object.

So, if you want to try the first of the preceding examples do the following:

To display a message box whenever your specific Word document opens

1. In the Word VBE, locate the document for which you want to add the event-handler in the Project Explorer and double-click the ThisDocument object for that document.

2. In the code window, select Document from the Object list. (When you do this, the default Document event is inserted in your code but you can delete it if you don’t need it after selecting the event you do want.)

3. After you select the Document object, the Procedure list is populated with all available Word document events. Select Open from the Procedure list to create the procedure for your event. (Document_Open)

4. Add the following code for your message box in this procedure (editing the message box text as needed):

   Private Sub Document_Open()
   MsgBox "When editing this document, please remember to do the following ... ", vbExclamation
   End Sub
   

5. After this event is added, the message box automatically displays each time the document is opened.

The other common type of event (which is available in all 2007 release programs that support VBA) is an event that is a member of the Application object. Using an application event is slightly more complex than using document-level events, but you can find easy, step-by-step instructions for Word application events in Using Events with the Application Object in the Word 2007 Developer Reference. Steps for using application objects vary slightly for some programs, but the steps provided in the referenced Word article are identical for several programs, including PowerPoint, Excel, and Visio. (Be careful, however, if using the Word instructions in another program, that you substitute the correct program name wherever Word is specified in the sample code.)

Note

As you see by the examples provided here, document-level events and application events can be used with similar types of document actions. You can easily find a list of all available events for each program using the Object Browser in the VBE or searching VBA help. Or, see SDK Documentation and References for Office Developers for links to all available online developer references for the 2007 release programs.

The event examples in this article are very simple examples to introduce what’s actually very powerful functionality. So, once you get comfortable with the basics of events, they are definitely worth exploring further.

Controlling One Program from Another

The last of the common types of actions for controlling built-in commands that we’ll explore in this article is how to execute commands in one program through VBA in another program. Because this topic comes last, you might expect it to be the most complex. If so, you’re about to be very pleasantly surprised.

For example, perhaps you need a macro that adds content from your Excel workbook into a Word document. Take the following steps:

1. In the Excel VBE, in Project Explorer, select the project in which you need to add the Word VBA code and then, on the Tools menu, click References. In the list of available references, find and select Microsoft Word 12.0 Object Library, and then click OK.

   After you’ve taken just this one step, the Word object model is available. If you type Word. In a procedure in that project, you’ll get IntelliSense AutoComplete lists for Word commands. In fact, as long as you start with Word., you could just leave it at that and write your code using a structure similar to the following sample:

   With Word.Application
       .Documents.Add
       .Documents(1).Range.Text = "Sample document text"
   End With
   

   However, please don’t leave it at that. If Word is not already running when you run a macro that uses Word code in this way, you’re out of luck. And, if you have to access Word more than once in the macro, that structure can be a bit cumbersome to say the least. Instead, take just one more step.

2. In the macro where you want to interact with Word, declare a variable as a Word application object and then set that variable to be a new instance of Word, so that you don’t have to be concerned about whether or not Word is already running. Doing that takes just two statements, as you see in the first two lines of the following macro (the Dim statement and then Set statement):

   Sub AddtoWordDoc()
   Dim myWd As Word.Application
   Set myWd = New Word.Application
   With myWd
       .Visible = True
       .Documents.Add
       With .Documents(1)
           .Range.Text = "Sample text."
           .SaveAs
       End With
       .Quit
   End With
   Set myWd = Nothing
   End Sub
   

   Notes:

   • Notice that, at the end of the macro execution, I quit the instance of Word that I created. This is an essential step to avoid overwhelming your system resources by adding multiple instances of an application.

   • If you copy the preceding code into an Excel module and give it a try, the Word session you create will be visible while it’s running and you’ll see the new document you created in the Word recent documents list after the code completes. If you prefer your Word session to come and go without notice, just remove the statement .Visible = True. The document can still be created and saved, but its actions won’t be visible to users.

   • If you’re writing code for others to use, remember that some networks may have security that prevents you from programmatically creating an instance of an application. So, as always, be sure to test your code in the environment where it will be used before sharing it.

That’s all there is to it! The same process works in any 2007 release program that supports VBA to access any other.

Time to Take Control

As you can probably imagine, the examples in this article are just scratching the surface of what you can do. So, when you feel that you’ve mastered the basics, take some time to further explore some of the concepts introduced here, and see just how far you can go with powerful functionality such as events.

In fact, if the power of features that let you control built-in commands or control one program from another has you curious about how much else you might be able to do, check out the additional resources that follow to learn about how far you can go with all aspects of Microsoft Office development.

Additional Resources

• Watch the Video: Using VBA to Control Built-In Commands in the 2007 Office System

• Extending Word 2007, Excel 2007, and PowerPoint 2007 with a Single Line of VBA Code

• Troubleshooting Word 2007 Documents More Easily Using VBA

• Using VBA to Format Long Word 2007 Documents in a Fraction of the Time

• 2007 Office System Reference Library on MSDN

• Getting Started with Office Development

• Microsoft Office Interactive Developer Map

• Webcast: Using VBA Every Day Is Easier Than You Think

• Webcast: Breaking Into Your Open XML Format Documents

• Advanced Microsoft Office Documents 2007 Edition Inside Out

About the Author

Stephanie Krieger is a Microsoft Office System MVP and the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out and Microsoft Office Document Designer. As a professional document consultant, Stephanie helps many global companies develop enterprise solutions for Microsoft Office on both platforms. She also frequently writes, presents, and creates content for Microsoft. You can reach Stephanie through her blog, arouet.net.