MS Word

Control a Macro with a Message Box

The Problem:

I've recorded a handful of complex macros, and boy, do they save me a bunch of time formatting and tweaking my documents! But sometimes I hit the hotkey for a macro by mistake, and it makes a whole bunch of changes to my document that I didn't need. It's my fault for being clumsy, but I'd sure like to be able to keep this from happening.

The Solution:

You can stop a macro while it's running by pressing Ctrl+Break. (Depending on your keyboard, you may find Break on the front face of the Pause key rather than on a key of its own.) But unless you're extremely quick off the mark, the macro will likely have executed many commands by the time you realize your mistake.

The best solution is to build a confirmation message box into each macro, telling you what the macro is going to do and giving you the chance to cancel it if you've run the macro by mistake or chosen the wrong macro.

To add a confirmation box to a macro, open it in the Visual Basic Editor (choose Tools » Macro » Macros, select the macro, and click the Edit button) and click in the Code window just after the comment lines if they are present, or after the Sub line if they are not. (The comment lines are the lines of text at the beginning of the macro that start with an apostrophe and are colored green by default. These are usually set off with a blank line above and below. You can use the comment lines to give the macro's name and a brief description of what it does.)

Press Enter to create a new line, press to move back to the new line, and type the code for a message box (see Figure 8-3), which should look like this:

VBA message box to make sure not to run a macro by mistake

    If MsgBox(Prompt:="Format this document?",
    Buttons:=vbYesNo + vbQuestion, _
      Title:="Format MD Report") = vbNo Then
      Exit Sub
    End If
Figure 8-3.

This MsgBox statement uses three arguments, named items that denote information used in the statement: Prompt, Buttons, and Title. Prompt receives a text string in double quotation marks (here, "Format this document"), which is displayed in the body of the message box. Title receives another string ("Format MD Report"), which appears in the titlebar. The Buttons argument controls both the buttons displayed in the message box and the icon (if any). vbYesNo makes the message box display a Yes button and a No button. vbQuestion makes the message box display a question-mark icon.

The If condition makes VBA check which button the user clicks in the message box. If the user clicks the No button, the result of the message box is vbNo. In this case, the Then statement comes into effect, and the Exit Sub command makes VBA exit the subprocedurein other words, skip the rest of the code in the macro. If the user clicks the Yes button, the result of the message box is vbYes; the Exit Sub command doesn't run in this case, so the rest of the macro does run. The End If statement marks the end of the If condition.

As you type the code for the message box, the Visual Basic Editor will help you out with prompts. When the Visual Basic Editor displays a drop-down list of possible options, you can choose from it by "typing down" (continuing typing) to reach your selection, by using and , or by using the mouse.

To test your message box, step into the macro by clicking anywhere between the Sub and End Sub lines and then pressing F8 to execute one command at a time. The Visual Basic Editor displays a yellow highlight on the command it's currently executing so you can track what's happening. When the message box is displayed, click the Yes button or the No button to dismiss it so you can continue executing the code.

by BrainBellupdated
Advertisement: