Playing with UserForms

Playing with UserForms

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 or a number or even select a range. You can generate an InputBox in two ways: by using a VBA function and 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 your code may need to convert the results to a value.

The prompt may consist of up to 1,024 characters. In addition, you can provide a title for the dialog box and a default value and specify its position on the screen. You can also specify a custom Help topic; if you do, the input box includes a Help button. The following example, which generates the dialog box shown in Figure , uses the VBA InputBox function to ask the user for his full name. The code then extracts the first name and displays a greeting in a message box.

Sub GetName()

Dim UserName As String

Dim FirstSpace As Integer

Do Until UserName <> ""

UserName = InputBox("Enter your full name: ", _

"Who Are You?")

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 isn’t found, the entire name is used as entered.

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 Below.

Table: Codes to Determine the Data Type Returned by Excel’s Inputbox Method

Code	Meaning
0	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

The EraseRange procedure, which follows, uses the ExcelInputBox method to allow the user to select a range to erase. The user can either type the range address manually or use the mouse to select the range in the sheet.

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

The VBA MsgBox Function

VBA’s MsgBox function is an easy way to display a message to the user or to get a simple response (such as OK or Cancel).

The syntax for MsgBox is as follows:

MsgBox(prompt[,buttons][,title][,helpfile, context])

·         prompt: Required. The text displayed in the message box.

·         buttons: Optional. A numeric expression that determines which buttons and icon are displayed in the message box. See Table

·         title: Optional. The caption in the message box window.

·         helpfile, context: Optional. The help file and Help topic.

You can easily customize your message boxes because of the flexibility of the buttons argument. (Table listed below the many constants that you can use for this argument.) You can specify which buttons to display, whether an icon appears, and which button is the default.

Constants Used for Buttons in the Msgbox Function in next post