Displaying Information to the User Displaying information is one of the best (and easiest ) ways to keep your users involved. For example, if an operation will take a long time, make sure the user has some visual clue about the progress of the operation. Similarly, if a user makes an error (for example, he enters the wrong argument in a user-defined function), he should be gently admonished so that he'll be less likely to repeat the error. This section presents several methods of displaying information to the users of your VBA applications. Displaying a Message in the Status Bar Most applications have a status bar at the bottom of the screen that's used for displaying messages and indicating the progress of the current operation. In Office 2003, Word, Excel, and Access let you display your own messages in the status bar. The next couple of sections show you how it's done. Status Bar Messages in Word and Excel For Word and Excel, you can use the Application object's StatusBar property to display text messages in the status bar at the bottom of the screen. This gives you an easy way to keep the user informed about what a procedure is doing or how much is left to process. Listing 12.2 demonstrates the StatusBar property using the InsertHyperlinks procedure that was called in Listing 12.1. Listing 12.2. A Procedure That Inserts Hyperlinks for a Specified Style of Heading Sub InsertHyperlinks(heading As String) Dim b As Bookmark Dim p As Paragraph Dim lastParagraph As Paragraph Dim anchorText As Range Dim strBookmark As String Dim totalParagraphs As Integer Dim i As Integer Dim j As Integer i = 0 j = 0 With ActiveDocument ' ' Delete the existing "Anchor" bookmarks ' For Each b In .Bookmarks If InStr(b.Name, "Anchor") Then b.Delete Next 'b ' ' Run through the paragraphs ' totalParagraphs = .Paragraphs.Count For Each p In .Paragraphs ' ' Display the progress in the status bar ' j = j + 1 Application.StatusBar = "Checking paragraph " _ & j & " of " & totalParagraphs ' ' Look for the specified style ' If p.Style = heading Then ' ' Create a bookmark ' i = i + 1 .Bookmarks.Add "Anchor" & i, p.Range ' ' Add a hyperlink for the heading ' Set lastParagraph = .Paragraphs(.Paragraphs.Count) Set anchorText = .Range(p.Range.Start, p.Range.End - 1) lastParagraph.Range.InsertParagraphAfter lastParagraph.Range.Hyperlinks.Add _ Anchor:=lastParagraph.Range, _ Address:="", _ SubAddress:="Anchor" & i, _ ScreenTip:=anchorText, _ TextToDisplay:=anchorText End If ' ' Delay briefly so the status bar text is visible ' startTime = Timer Do While Timer - startTime < 0.45 DoEvents Loop Next 'p Application.StatusBar = "" End With End Sub This procedure runs through every paragraph in the active document and looks for those paragraphs that use whatever style is specified as the heading argument. When it finds such a paragraph, a hyperlink to that paragraph is inserted at the bottom of the document. To keep the user informed of the progress of the operation, the status bar is updated with each pass, as shown in the following snippet: totalParagraphs = .Paragraphs.Count For Each p In .Paragraphs ' ' Display the progress in the status bar ' j = j + 1 Application.StatusBar = "Checking paragraph " _ & j & " of " & totalParagraphs The variable totalParagraphs stores the total number of paragraphs, and the variable j counts the paragraphs. The StatusBar property is then used to display a message such as the following: Checking paragraph 1 of 208  | I don't discuss programming hyperlinks in this book, but the text in the example file ( Chapter12.doc ) contains a tutorial on creating and working with hyperlinks via VBA. |
This loop executes quickly in a small document, so I've included a loop that delays the main loop for about half a second so that you can see the changes in the status bar. You generally wouldn't include such a delay in your code. When the loop is done, the procedure sets the StatusBar property to the null string ("") to clear the status bar. Programming the Status Bar in Access Access uses the status bar to display messages and to display progress meters that let you know the progress of a long operation (such as importing records). You can use the SysCmd function to provide the same feedback to the users of your Access applications. Here's the syntax: SysCmd( Action, Text, Value ) | Action | A constant that specifies what Access does to the status bar: | | | acSysCmdInitMeter | Initializes the progress meter. | | | acSysCmdUpdateMeter | Updates the progress meter. | | | acSysCmdRemoveMeter | Removes the progress meter. | | | acSysCmdSetStatus | Displays Text in the status bar. | | | acSysCmdClearStatus | Clears the status bar. | | Text | The text to be displayed in the status bar. You must specify this argument when Action is acSysCmdInitMeter or acSysCmdSetStatus . | | Value | Controls the display of the progress meter. You must specify this argument when Action is acSysCmdInitMeter or acSysCmdUpdateMeter . | | | The Access code listings for this chapter can be found on my Web site in the file named Chaptr12.txt : http://www.mcfedries.com/ABGVBA/Chapter12.txt |
If you just want to display text in the status bar, use acSysCmdSetStatus for the Action argument and specify the status bar text with the Text argument. Listing 12.3 shows a procedure that opens a form and then loops , depending on the number of controls in the form. While in the loop, the status bar message is updated to indicate the progress of the loop. To slow things down a bit, a Do While loop delays for half a second on each pass through the main loop. Listing 12.3. A Procedure That Displays Text in the Access Status Bar Sub StatusBarText() Dim frm As Form Dim strStatus As String Dim ctrlCount As Integer Dim i As Integer Dim start As Long ' ' Open the Orders form ' DoCmd.OpenForm "Startup", acDesign Set frm = Forms("Startup") ' ' Get the control count ' ctrlCount = frm.Controls.Count ' ' Loop ctrlCount times ' For i = 1 To ctrlCount ' ' Update the status bar text ' strStatus = "Control " & i & " of " & ctrlCount SysCmd acSysCmdSetStatus, strStatus ' ' Delay for half a second ' start = Timer Do While Timer < (start + 0.5) DoEvents Loop Next i ' ' Clear the status bar ' SysCmd acSysCmdClearStatus End Sub Using a progress meter involves three steps: -
Run SysCmd with acSysCmdInitMeter to initialize the progress bar. Here, you use the Text argument to specify text that will appear in front of the progress meter, and you use the Value argument to set the maximum value of the meter. -
During the operation whose progress you want to show, run the SysCmd function at regular intervals. In this case, the Action argument is acSysCmdUpdateMeter and you use the Value argument to specify the current value of the meter. For example, if your maximum progress meter value is 100 and you update the meter to 50, the meter will appear half filled in. -
When the operation is complete, run SysCmd once again using acSysCmdRemoveMeter as the Action argument to clear the status bar. Listing 12.4 shows a slightly different example that uses a progress meter instead of text to indicate the loop's progress. Listing 12.4. A Procedure That Displays a Progress Meter in the Access Status Bar Sub StatusBarProgressMeter() Dim frm As Form Dim ctrlCount As Integer Dim i As Integer Dim start As Long ' ' Open the Orders form ' DoCmd.OpenForm "Startup", acDesign Set frm = Forms!Startup ' ' Get the control count ' ctrlCount = frm.Controls.Count ' ' Initialize the progress meter ' SysCmd acSysCmdInitMeter, "Control Loop:", ctrlCount ' ' Loop ctrlCount times ' For i = 1 To ctrlCount ' ' Update the progress meter ' SysCmd acSysCmdUpdateMeter, i ' ' Delay for half a second ' start = Timer Do While Timer < (start + 0.5) DoEvents Loop Next i ' ' Clear the status bar ' SysCmd acSysCmdRemoveMeter End Sub Displaying a Message Using MsgBox The problem with using the StatusBar property to display messages is that it's often a bit too subtle. Unless the user knows to look in the status bar, she might miss your messages altogether. When the user really needs to see a message, you can use the MsgBox function: MsgBox( Prompt, Buttons, Title, HelpFile, Context ) | | The MsgBox function, like all VBA functions, needs parentheses around its arguments only when you use the function's return value. See the section later in this chapter called "Getting Return Values from the Message Dialog Box" to learn about the return values produced by the MsgBox function. |
| Prompt | The message you want to display in the dialog box. (You can enter a string up to 1,024 characters long.) | | Buttons | (optional) A number or constant that specifies, among other things, the command buttons that appear in the dialog box. (See the next section.) The default value is 0. | | Title | (optional) The text that appears in the dialog box title bar. If you omit the title, VBA uses the name of the underlying application (for example, Microsoft Excel). | | HelpFile | (optional) The text that specifies the Help file that contains the custom help topic. (I don't discuss custom help topics in this book.) If you enter HelpFile , you also have to include Context . If you include HelpFile , a Help button appears in the dialog box. | | Context | (optional) A number that identifies the help topic in HelpFile . | tip  | For long prompts, VBA wraps the text inside the dialog box. If you would prefer to create your own line breaks, use either VBA's Chr(13) function or VBA's vbCr constant to insert a carriage -return character between each line: MsgBox "First line" & Chr(13) & "Second line" MsgBox "First line" & vbCr & "Second line" |
For example, the following statement displays the message dialog box shown in Figure 12.1: MsgBox "You must enter a number between 1 and 100!",,"Warning" Figure 12.1. A simple message dialog box produced by the MsgBox function.  Setting the Style of the Message The default message dialog box displays only an OK button. You can include other buttons and icons in the dialog box by using different values for the Buttons parameter. Table 12.1 lists the available options. Table 12.1. The MsgBox buttons Parameter Options | Constant | Value | Description | | Buttons | | vbOKOnly | | Displays only an OK button. (This is the default.) | | vbOKCancel | 1 | Displays the OK and Cancel buttons. | | vbAbortRetryIgnore | 2 | Displays the Abort, Retry, and Ignore buttons. | | vbYesNoCancel | 3 | Displays the Yes, No, and Cancel buttons. | | vbYesNo | 4 | Displays the Yes and No buttons. | | vbRetryCancel | 5 | Displays the Retry and Cancel buttons. | | Icons | | vbCritical | 16 | Displays the Critical Message icon. | | vbQuestion | 32 | Displays the Warning Query icon. | | vbExclamation | 48 | Displays the Warning Message icon. | | vbInformation | 64 | Displays the Information Message icon. | | Default Button | | vbDefaultButton1 | | The first button is the default (that is, the button selected when the user presses Enter). | | vbDefaultButton2 | 256 | The second button is the default. | | vbDefaultButton3 | 512 | The third button is the default. | | Modality | | vbApplicationModal | | The user must respond to the message box before continuing work in the current application. | | vbSystemModal | 4096 | All applications are suspended until the user responds to the message box. | You derive the Buttons argument in one of two ways: For example, Listing 12.5 shows a procedure named ButtonTest , and Figure 12.2 shows the resulting dialog box. Here, three variables msgPrompt , msgButtons , and msgTitle store the values for the MsgBox function's Prompt , Buttons , and Title arguments, respectively. In particular, the following statement derives the Buttons argument: msgButtons = vbYesNo + vbQuestion + vbDefaultButton2 Figure 12.2. The dialog box that's displayed when you run the code in Listing 12.5.  You also could derive the Buttons argument by adding up the values that these constants represent (4, 32, and 256, respectively), but the procedure becomes less readable that way. Listing 12.5. A Procedure that Creates a Message Dialog Box Sub ButtonTest() Dim msgPrompt As String, msgTitle As String Dim msgButtons As Integer, msgResult As Integer msgPrompt = "Are you sure you want to insert " & Chr(13) & _ "the heading hyperlinks?" msgButtons = vbYesNo + vbQuestion + vbDefaultButton2 msgTitle = "Insert Heading Hyperlinks" msgResult = MsgBox(msgPrompt, msgButtons, msgTitle) End Sub Getting Return Values from the Message Dialog Box A message dialog box that displays only an OK button is straightforward. The user either clicks OK or presses Enter to remove the dialog from the screen. The multibutton styles are a little different, however; the user has a choice of buttons to select, and your procedure should have a way to find out which button the user chose. You do this by storing the MsgBox function's return value in a variable. Table 12.2 lists the seven possible return values. Table 12.2. The MsgBox Function's Return Values | Constant | Value | Button Selected | | vbOK | 1 | OK | | vbCancel | 2 | Cancel | | vbAbort | 3 | Abort | | vbRetry | 4 | Retry | | vbIgnore | 5 | Ignore | | vbYes | 6 | Yes | | vbNo | 7 | No | To process the return value, you can use an If...Then...Else or Select Case structure to test for the appropriate values. For example, the ButtonTest procedure shown earlier used a variable called msgResult to store the return value of the MsgBox function. Listing 12.6 shows a revised version of ButtonTest that uses a Select Case statement to test for the three possible return values. Listing 12.6. This Example Uses Select Case to Test the Return Value of the MsgBox Function Sub ButtonTest2() Dim msgPrompt As String, msgTitle As String Dim msgButtons As Integer, msgResult As Integer msgPrompt = "Do you want to insert the Main Heading hyperlinks?" & _ vbCr & vbCr & _ "Yes = Insert links for Main Heading style" & vbCr & _ "No = Insert links for Listing style" & vbCr & _ "Cancel = No links inserted" msgButtons = vbYesNoCancel + vbQuestion + vbDefaultButton1 msgTitle = "Insert Hyperlinks" msgResult = MsgBox(msgPrompt, msgButtons, msgTitle) Select Case msgResult Case vbYes InsertHyperlinks "Main Heading" Case vbNo InsertHyperlinks "Listing" Case vbCancel Exit Sub End Select End Sub The result is stored in the msgResult variable and then a Select Case structure handles the three possibilities: -
The user clicks Yes In this case, msgResult is vbYes , so the code runs the InsertHyperlinks procedure and passes "Main Heading" as the parameter. -
The user clicks No In this case, msgResult is vbNo . This time, the code runs the InsertHyperlinks procedure using "Listing" as the parameter. -
The user clicks Cancel In this case, the code runs Exit Sub to cancel the procedure. (This is a bit redundant since the procedure would exit anyway after the Select Case was done. However, this is a good trick to remember in cases where the procedure would run other code if you didn't exit the procedure explicitly using Exit Sub . ) | 