Introduction to Using Events


Note 

This and the remalning sections of this chapter are intended to provide a taste of some additional capabilities of VBA beyond the basic macro, for those who feel confident with the primer content to this point and would like to try something a bit more complex. These sections assume you’ve already mastered everything included in the primer to this point.

Events in VBA are procedures that run automatically when a specified event occurs. There are several types of events in VBA, such as those that occur with a specific document action (such as opening a document), an application-specific action (such as exiting the program), or an action taken in a dialog box (such as clicking a command button). In this section, the concept of events is introduced using document-level events.

The method shown here for adding document-level events to a project is available in Word and Excel. This is where we get to use the ThisDocument or ThisWorkbook object that you see attached to any project in the Project Explorer.

Most of the work to create the event is done for you using the drop-down lists at the top of the Code window. When you select Document (or Workbook in Excel) from the Object list at the top left of the window, as shown in the following image, the Procedure list at the top right of the window is populated with the available events.

image from book

Many more events are available in Excel than in Word, but the basics (such as open, save, close, and print) are available in both.

Document-level events can be simple or complex, just as any macro. At the simplest, say that you’re working on an important, complex document with other document editors, and it’s essential that all editors are aware of certain formatting requirements. The particular requirements involve table layout or other specifications that can’t be controlled through document protection or any other built-in features. Adding a comment in the document would be fine, but this document keeps getting messed up, the deadline is looming, and so you want those formatting requirements to pop out at anyone who opens the document. Just create an event that displays a message box with your instructions, whenever the document is open.

To do this, take the following steps.

  1. In the Word Visual Basic Editor, double-click the ThisDocument object and then select Document from the Object list.

    When you do this, a Document_New event is created for you because this is the default event type. If you don’t want a Document_New event, you can delete it later.

  2. To create a Document_Open event, select Open from the Procedure list.

    Notice that the Sub and End Sub statements are automatically created for you, along with the procedure name. Don’t change the procedure name, because the name is what tells VBA this is a procedure to be run automatically under the specified circumstance.

  3. Type the code for your macro. In this case, it’s just a message box. Then, save and close the document.

The code for the event you just created would look something like this:

 Sub Document_Open() MsgBox "<text of your instructions>", vbCritical, "IMPORTANT!" End Sub

Then, the next time that document is opened (and each subsequent time), a message box similar to the following image appears automatically.

image from book

Note 

To create line breaks and space between lines in a message box, as you see here, an easy option is to use the vbCr constant for adding a carriage return. Just place text portions of the string in quotation marks and separate each phrase with an ampersand. So, for example, to make a single line break, you would write "<message text>" & vbCr & "<message text>". To create two consecutive line breaks (space between lines), you would put two vbCr constants, each between separate ampersands, between the text phrases in quotation marks (such as..." & vbCr & vbCr & "...). Remember that constants, being VBA terms, are never surrounded by quotation marks, even when they appear as part of a text string.

Note that some document-level events require arguments between the parentheses in the Sub statement. When they do, those arguments are provided for you. For general macros of the type discussed in this primer, you don’t need to be concerned at all with those declarations.

In Excel, notice that you can create document-level events for the workbook or for an individual sheet. To create an event procedure for a specific sheet, or just to view what events are available to worksheets, double-click a sheet object in Project Explorer instead of the ThisWorkbook object. When you select Worksheet from the Object list, you can see the available worksheet procedures in the Procedure list.

If you don’t see an event you want in Word, you might be able to accomplish what you need using Word VBA to write procedures for controlling built-in Word commands. For example, Excel workbooks have an available event named BeforePrint, but Word documents do not. However, in Word, you can use VBA to control the built-in Word print command. For information on where you can learn how to use VBA to control built-in Word commands or to get information about application-level events, see “Next Steps for Working with Automation in Microsoft Office” on page 1141.




2007 Microsoft Office System Inside Out
2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)
ISBN: 0735623244
EAN: 2147483647
Year: 2007
Pages: 299

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net