Getting Input from the User As you've seen, the MsgBox function lets your procedures interact with the user and get some feedback. Unfortunately, this method limits you to simple command-button responses. For more varied user input, you need to use more sophisticated techniques. The rest of this chapter shows you two such methods : prompting the user for input and accessing an application's built-in dialog boxes. Prompting the User for Input The InputBox function displays a dialog box with a message that prompts the user to enter data, and it provides a text box for the data itself. Here's the syntax for this function: InputBox( Prompt, Title, Default, Xpos, Ypos, HelpFile, Context ) | Prompt | The message you want to display in the dialog box (1,024-character maximum). | | Title | (optional) The text that appears in the dialog box title bar. The default value is the null string (nothing). | | Default | (optional) The default value displayed in the text box. If you omit Default , the text box is displayed empty. | | Xpos | (optional) The horizontal position of the dialog box from the left edge of the screen. The value is measured in points (there are 72 points in an inch). If you omit Xpos , the dialog box is centered horizontally. | | Ypos | (optional) The vertical position, in points, from the top of the screen. If you omit Ypos , the dialog is centered vertically in the current window. | | HelpFile | (optional) The text specifying the Help file that contains the custom help topic. (Again, I don't cover Help files 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 . | For example, Listing 12.7 shows a procedure called GetInterestRate that uses the InputBox method to prompt the user for an interest rate value. Figure 12.3 shows the dialog box that appears. Listing 12.7. A Procedure That Prompts the User for an Interest Rate Value Function GetInterestRate() Dim done As Boolean ' ' Initialize the loop variable ' done = False While Not done ' ' Get the interest rate ' GetInterestRate = InputBox( _ Prompt:="Enter an interest rate between 0 and 1:", _ Title:="Enter Interest Rate") ' ' First, check to see if the user cancelled ' If GetInterestRate = "" Then GetInterestRate = 0 Exit Function Else ' ' Now make sure the entered rate is betwen 0 and 1 ' If GetInterestRate >= 0 And GetInterestRate <= 1 Then done = True End If End If Wend End Function Figure 12.3. A dialog box generated by the InputBox function in Listing 12.7.  The InputBox method returns one of the following values: In Listing 12.7, the result of the InputBox method is stored in the GetInterestRate variable. The procedure first checks to see if InputBox returned the empty string (""). If so, the Exit Function statement bails out of the procedure. Otherwise, an If...Then statement checks to make sure the number is between 0 and 1. If it is, the done variable is set to True so that the While...Wend loop will exit; if the number isn't between 0 and 1, the procedure loops and the dialog box is redisplayed. It's also worth noting here that Excel has its own version of InputBox that's an Application object method. It has the same syntax as the VBA InputBox function, except that it tacks on an extra argument ” Type : Application.InputBox(Prompt, Title, Default, Xpos, Ypos, HelpFile, Context, Type ) Here, Type is an optional number that specifies the data type of the return value, as follows : | Type | Data Type | | | Formula | | 1 | Number | | 2 | Text (the default) | | 4 | Boolean (True or False) | | 8 | Reference (a Range object) | | 16 | Error value | | 32 | An array of values | Accessing an Application's Built-In Dialog Boxes Many VBA methods are known as dialog box equivalents because they let you select the same options that are available in an application's built-in dialog boxes. Using dialog box equivalents works fine if your procedure knows which options to select, but there are times when you might want the user to specify some of the dialog box options.  | To see a complete list of constants for Word and Excel's built-in dialog boxes, first open the Object Browser by selecting View, Object Browser . In the list of libraries, select the application (such as Excel or Word), and highlight < globals > in the Classes list. In the Member list, look for the xx Dialog constants, where xx varies between applications: wdDialog for Word and xlDialog for Excel. |
For example, if your procedure will print a document (using the PrintOut method), you might need to know how many copies the user wants or how many pages to print. You could use the InputBox method to get this data, but it's usually easier to just display the Print dialog box. The built-in dialog boxes are Dialog objects, and Dialogs is the collection of all the built-in dialog boxes. Note that these objects are implemented only in Word and Excel. To reference a particular dialog box, use one of the predefined application constants. Table 12.3 lists a few of the more common ones from Word and Excel. Table 12.3. Some of Word and Excel's Built-in Dialog Box Constants | Word Constant | Excel Constant | Dialog Box | | wdDialogFormatFont | xlDialogFont | Font | | wdDialogFileNew | xlDialogNew | New | | wdDialogFileOpen | xlDialogOpen | Open | | wdDialogFilePageSetup | xlDialogPageSetup | Page Setup | | wdDialogEditPasteSpecial | xlDialogPasteSpecial | Paste Special | | wdDialogFilePrint | xlDialogPrint | Print | | wdDialogFilePrintSetup | xlDialogPrinterSetup | Printer Setup | | wdDialogFileSaveAs | xlDialogSaveAs | Save As | | wdDialogInsertObject | xlDialogObject | Object | | wdDialogFormatStyle | xlDialogStyle | Style | | wdDialogTableSort | xlDialogSort | Sort | To display any of these dialog boxes, use the Dialog object's Show method. For example, the following statement displays Excel's Print dialog box: Application.Dialogs(xlDialogPrint).Show If the user clicks Cancel to exit the dialog box, the Show method returns False. This means that you can use Show inside an If statement to determine what the user did: If Not Application.Dialogs(xlDialogPrint).Show Then MsgBox "File was not printed" End If Note, too, that the Show method can take arguments. For example, Word's Show method uses the following syntax: Dialog .Show( Timeout ) | Dialog | The Dialog object you want to show. | | Timeout | The time, in thousandths of a second, after which the dialog box is dismissed. (Changes made by the user are accepted.) | For example, the following statement shows the Font dialog box, and then dismisses it after approximately 10 seconds: Application.Dialogs(wdDialogFormatFont).Show 10000 Here's the syntax for Excel's Show method: Dialog .Show( Arg1, Arg2. ...) | Dialog | The Dialog object you want to show. | | Arg1 , Arg2 , | These arguments represent specific controls in the dialog box, and they enable you to set the value of the controls in advance. | For example, here's the syntax for Excel's Font dialog box: Application.Dialogs(xlDialogFont).Show name_text, size_num Here, name_text and size_num represent the Face and Size controls, respectively, in the Font dialog box. The following statement shows Excel's Font dialog box, and it sets the Face list to Garamond and the Size list to 16: Application.Dialogs(xlDialogFont).Show "Garamond", 16 To do the same thing in Word, you use the predefined argument names as though they were properties of the specified Dialog object. For example, you use Font to return or set the Font control value in the Font dialog box: With Dialogs(wdDialogFormatFont) .Font = "Garamond" .Show End With | | To see a complete list of the control arguments used by Word and Excel, see the following Microsoft Web sites: Word: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ vbawd10/html/wohowDialogArguments.asp Excel: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ vbawd10/html/wohowDialogArguments.asp |
Word's Dialog object is much more flexible and powerful than Excel's in that it supports extra properties and methods. For example, the DefaultTab property enables you to specify which dialog box tab has the focus when you display a dialog box. Here's an example that displays the Save tab in the Options dialog box: With Application.Dialogs(wdDialogToolsOptions) .DefaultTab = wdDialogToolsOptionsTabSave .Show End With Word's Dialog object also has a Display method that uses a syntax similar to that of the Show method: Dialog .Display( Timeout ) | Dialog | The Dialog object you want to show. | | Timeout | The time, in thousandths of a second, after which the dialog box is dismissed. | The difference is that if you specify a Timeout value, when the dialog box is dismissed after the specified time, Word does not accept the user's changes. Another Dialog object method is Execute , which runs the dialog box without showing it to the user. Listing 12.8 shows an example. Listing 12.8. A Function Procedure That Uses Word's Word Count Dialog Box to Get the Total Number of Words in the Active Document Function CountDocumentWords() As Long With Dialogs(wdDialogToolsWordCount) .Execute CountDocumentWords = .Words End With End Function Sub DisplayWordCount() MsgBox "This document contains " & CountDocumentWords & " words." End Sub This procedure uses Execute to run the Word Count dialog box, and then uses the Words argument to return the number of words in the document. The Absolute Minimum This chapter introduced you to a few methods for interacting with the users of your VBA applications (including yourself). You began with a look at sounds, including the simple Beep function and PowerPoint's more sophisticated SoundEffects object. From there, you progressed to displaying information to the user. You learned how to display messages in the status bar and how to display a message dialog box with the MsgBox function. You closed this chapter with two techniques for getting input from the user: the InputBox function and the application's built-in dialog boxes. Here's a list of chapters where you'll find related information on user interaction: -
To get maximum control over your code's user interaction, you'll need to build your own custom dialog boxes and user forms. To find out how, see Chapter 13, "Creating Custom VBA Dialog Boxes." -
To make it easier for users to interact with your procedures, you can assign your procedures to menus and toolbars. I show you how this is done in Chapter 14, "Creating Custom Menus and Toolbars ." -
A proper application interface shields the user from program errors. I show you a few techniques for doing this in Chapter 15, "Debugging VBA Procedures." |
| 