Using an Input Box
An input box is a simple dialog box that allows the user to make a single entry. For example, you can use an input box to let the user enter text, a number, or even select a range. There are actually two ways to generate an InputBox : one by using a VBA function, and the other by using a method of the Application object.
The VBA InputBox function
The syntax for VBA's InputBox function is:
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, context])
-
prompt : Required. The text displayed in the InputBox.
-
title : Optional. The caption of the InputBox window.
-
default : Optional. The default value to be displayed in the dialog box.
-
xpos , ypos : Optional. The screen coordinates of the upper-left corner of the window.
-
helpfile , context : Optional. The help file and help topic.
The InputBox function prompts the user for a single piece of information. The function always returns a string, so it may be necessary to convert the results to a value.
The prompt may consist of about 1,024 characters (more or less, depending on the width of the characters used). In addition, you can provide a title for the dialog box and a default value and specify its position on the screen. And you can specify a custom help topic; if you do, the input box includes a Help button.
The following example, whose output is shown in Figure 12-1, uses the VBA InputBox function to ask the user for his or her full name . The code then extracts the first name and displays a greeting in a message box.
Figure 12-1: VBA's InputBox function at work.
Sub GetName() Dim UserName As String Dim FirstSpace As Integer Do Until UserName <> "" UserName = InputBox("Enter your full name: ", _ "Identify Yourself") Loop FirstSpace = InStr(UserName, " ") If FirstSpace <> 0 Then UserName = Left(UserName, FirstSpace - 1) End If MsgBox "Hello " & UserName End Sub Notice that this InputBox function is written in a Do Until loop to ensure that something is entered when the input box appears. If the user clicks Cancel or doesn't enter any text, UserName contains an empty string, and the input box reappears. The procedure then attempts to extract the first name by searching for the first space character (by using the InStr function) and then using the Left function to extract all characters before the first space. If a space character is not found, the entire name is used as entered.
As I mentioned, the InputBox function always returns a string. If the string returned by the InputBox function looks like a number, you can convert it to a value by using VBA's Val function. Or you can use Excel's InputBox method, which I describe in the next section.
Figure 12-2 shows another example of the VBA InputBox function. The user is asked to fill in the missing word. This example also illustrates the use of named arguments. The prompt text is retrieved from a worksheet cell .
Figure 12-2: Using VBA's InputBox function with a long prompt.
Sub GetWord() Dim TheWord As String Dim p As String Dim t As String p = Range("A1") t = "What's the missing word?" TheWord = InputBox(prompt:=p, Title:=t) If UCase(TheWord) = "BATTLEFIELD" Then MsgBox "Correct." Else MsgBox "That is incorrect." End If End Sub | CD-ROM | The two examples in this section are available on the companion CD-ROM. The file is named |
VBA inputbox.xlsm The Excel InputBox method
Using Excel's InputBox method offers three advantages over VBA's InputBox function:
-
You can specify the data type returned.
-
The user can specify a worksheet range by dragging in the worksheet.
-
Input validation is performed automatically.
The syntax for the Excel InputBox method is:
InputBox(Prompt [,Title][,Default][,Left][,Top][,HelpFile, HelpContextID] [,Type])
-
Prompt : Required. The text displayed in the input box.
-
Title : Optional. The caption in the input box window.
-
Default : Optional. The default value to be returned by the function if the user enters nothing.
-
Left , Top : Optional. The screen coordinates of the upper-left corner of the window.
-
HelpFile , HelpContextID : Optional. The help file and help topic.
-
Type : Optional. A code for the data type returned, as listed in Table 12-1.
| Code | Meaning |
|---|---|
|
| A formula |
| 1 | A number |
| 2 | A string (text) |
| 4 | A logical value ( True or False ) |
| 8 | A cell reference, as a range object |
| 16 | An error value, such as #N/A |
| 64 | An array of values |
Open table as spreadsheet Excel's InputBox method is quite versatile. To allow more than one data type to be returned, use the sum of the pertinent codes. For example, to display an input box that can accept text or numbers , set type equal to 3 (that is, 1 + 2, or number plus text ). If you use 8 for the type argument, the user can enter a cell or range address manually or point to a range in the worksheet.
The EraseRange procedure, which follows , uses the InputBox method to allow the user to select a range to erase (see Figure 12-3). The user can either type the range address manually or use the mouse to select the range in the sheet.
Figure 12-3: Using the InputBox method to specify a range.
The InputBox method with a type argument of 8 returns a Range object (note the Set keyword). This range is then erased (by using the Clear method). The default value displayed in the input box is the current selection's address. The On Error statement ends the procedure if the input box is canceled .
Sub EraseRange() Dim UserRange As Range On Error GoTo Canceled Set UserRange = Application.InputBox _ (Prompt:="Range to erase:", _ Title:="Range Erase", _ Default:=Selection.Address, _ Type:=8) UserRange.Clear UserRange.Select Canceled: End Sub
| CD-ROM | This example is available on the companion CD-ROM in a file named |
Yet another advantage of using Excel's InputBox method is that Excel performs input validation automatically. In the GetRange example, if you enter something other than a range address, Excel displays an informative message and lets the user try again (see Figure 12-4).
Figure 12-4: Excel's InputBox method performs validation automatically.
EAN: 2147483647
Pages: 319