Using the InputBox function

The InputBox function is useful for obtaining a single input from the user. A simplified version of the function's syntax follows:

InputBox(prompt[,title][,default])

The elements are defined as follows:

• prompt (Required): Text that is displayed in the input box
• title (Optional): Text that appears in the input box's title bar
• default (Optional): The value that appears in the input box's text area by default

The following is an example of how you can use the InputBox function:

CName = InputBox("Customer name?","Customer Data")

When this VBA statement is executed, Excel displays the dialog box shown in Figure 41.2. Notice that this example uses only the first two arguments for the InputBox function and does not supply a default value. When the user enters a value and clicks OK, the value is assigned to the variable CName. Your VBA code can then use that variable.

Using the MsgBox function

The VBA MsgBox function is a handy way to display information and to solicit simple input from users. We use the VBA MsgBox function in many of this book's examples to display a variable's value. A simplified version of the MsgBox syntax is as follows:

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

The elements are defined as follows:

• prompt (Required): Text that is displayed in the message box
• buttons (Optional): The code for the buttons that appear in the message box
• title (Optional): Text that appears in the message box's title bar

You can use the MsgBox function by itself or assign its result to a variable. If you use it by itself, don't include parentheses around the arguments. The following example displays a message and does not return a result:

Sub MsgBoxDemo()
  MsgBox "Click OK to continue"
End Sub

Figure 41.3 shows how this message box appears.

To get a response from a message box, you can assign the result of the MsgBox function to a variable. The following code uses some built-in constants (described in Table 41.1) to make it easier to work with the values that are returned by MsgBox:

Sub GetAnswer()
  Ans = MsgBox("Continue?", vbYesNo)
  Select Case Ans
    Case vbYes
'      ...[code if Ans is Yes]...
    Case vbNo
'      ...[code if Ans is No]...
  End Select
End Sub 

When this procedure is executed, the Ans variable contains a value that corresponds to vbYes or vbNo. The Select Case statement determines the action to take based on the value of Ans.

You can easily customize your message boxes because of the flexibility of the buttons argument. Table 41.1 lists the most common built-in constants that you can use for the buttons argument. You can specify which buttons to display, whether an icon appears, and which button is the default.

The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo) and a question mark icon (vbQuestion). The second button (the No button) is designated as the default button (vbDefaultButton2), which is the one that is executed if the user presses Enter. For simplicity, these constants are assigned to the Config variable, and Config is then used as the second argument in the MsgBox function.

Sub GetAnswer()
  Config = vbYesNo + vbQuestion + vbDefaultButton2
  Ans = MsgBox("Process the monthly report?", Config)
  If Ans = vbYes Then RunReport
  If Ans = vbNo Then Exit Sub
End Sub

Figure 41.4 shows how this message box appears when the GetAnswer procedure is executed. If the user clicks the Yes button, the routine executes the procedure named RunReport (which is not shown). If the user clicks the No button (or presses Enter), the procedure is ended with no action. Because the title argument was omitted in the MsgBox function, Excel uses the default title (Microsoft Excel).

The procedure that follows is another example of using the MsgBox function:

Sub GetAnswer2()
  Msg = "Do you want to process the monthly report?"
  Msg = Msg & vbNewLine & vbNewLine
  Msg = Msg & "Processing the monthly report will take approximately "
  Msg = Msg & "15 minutes. It will generate a 30-page report for all "
  Msg = Msg & "sales offices for the current month."
  Title = "XYZ Marketing Company"
  Config = vbYesNo + vbQuestion
  Ans = MsgBox(Msg, Config, Title)
  If Ans = vbYes Then RunReport
  If Ans = vbNo Then Exit Sub
End Sub

This example demonstrates an efficient way to specify a longer message in a message box. A variable (Msg) and the concatenation operator (&) are used to build the message in a series of statements. vbNewLine is a constant that represents a line break character. (Using two line breaks will insert a blank line.) The title argument is also used to display a different title in the message box. The Config variable stores the constants that generate Yes and No buttons and a question mark icon. Figure 41.5 shows how this message box appears when the procedure is executed.

Working with UserForms

To create a dialog box, you must first insert a new UserForm in the VBE. To activate the VBE, choose Developer ➪ Code ➪ Visual Basic (or press Alt+F11). Make sure that the correct workbook is selected in the Project window and then choose Insert ➪ UserForm. The VBE displays an empty UserForm, as shown in Figure 41.6. When you activate a UserForm, the VBE displays the Toolbox, which is used to add controls to the UserForm.

Adding controls

The Toolbox, also shown in Figure 41.6, contains ActiveX controls that you can add to your UserForm. If the Toolbox is not visible, choose View ➪ Toolbox.

When you move the mouse pointer over a control in the Toolbox, the control's name appears. To add a control, select the control in Toolbox and either click in the form to get the default size or drag the desired size onto the form. After adding a control, you can move it or change its size.

Table 41.2 lists the Toolbox controls.

Changing the properties of a control

Every control that you add to a UserForm has several properties that determine the way the control looks and behaves. You can change some of these properties (such as Height and Width) by clicking and dragging the control's border. To change other properties, use the Properties window.

To display the Properties window, choose View ➪ Properties Window (or press F4). The Properties window displays a list of properties for the selected control. (Each type of control has a different set of properties.) If you click the UserForm itself, the Properties window displays properties for the form. Figure 41.7 shows the Properties window for a CommandButton control.

To change a property, select the property in the Properties window and then enter a new value. Some properties (such as BackColor) let you select a value from a list. The top of the Properties window contains a drop-down list of all the controls on the form. You can also click a control to select it and display its properties.

When you set properties via the Properties window, you're setting properties at design time. You can also use VBA to change the properties of controls while the UserForm is displayed (that is, at runtime).

A complete discussion of all the properties is well beyond the scope of this book—and it would indeed be dull reading. To find out about a particular property, select it in the Properties window and press F1 for help.

Handling events

When you insert a UserForm, that form can also hold VBA Sub procedures to handle the events that are generated by the UserForm. An event is something that occurs when the user manipulates a control. For example, clicking a button causes a Click event. Selecting an item in a list box control also triggers a Click event, as well as a Change event. To make a UserForm useful, you must write VBA code to do something when an event occurs.

Event handler procedures have names that combine the control with the event. The general form is the control's name, followed by an underscore and then the event name. For example, the procedure that is executed when the user clicks a button named MyButton is MyButton_Click. You don't have to remember how they're named, however. Just right-click the control and choose View Code. The Private Sub and End Sub keywords will be inserted for you, and the correct name of one of the control's events will already be constructed. Use the drop-downs at the top of the code pane to change the event from the default.

Figure 41.8 shows the result of right-clicking on a CommandButton control named CommandButton1 and choosing View Code. The CommandButton's default event, the Click event, is inserted into the code module. The figure also shows some of the other events available using the code pane's drop-down.

Displaying a UserForm

You also need to write a procedure to display the UserForm. You use the Show method of the UserForm object. The following procedure displays the UserForm named UserForm1:

Sub ShowDialog()
  UserForm1.Show
End Sub

This procedure should be stored in a regular VBA module (not the code module for the UserForm). If your VB project doesn't have a regular VBA module, choose Insert ➪ Module to add one.

When the ShowDialog procedure is executed, the UserForm is displayed. What happens next depends on the event handler procedures that you create.

Creating the UserForm

If you're following along on your computer, start with a new workbook. Then follow these steps:

1. Choose Developer ➪ Code ➪ Visual Basic (or press Alt+F11). The VBE appears.
2. Click your workbook's name in the Project Explorer to activate it.
3. Choose Insert ➪ UserForm. The VBE adds an empty form named UserForm1 and displays the Toolbox.
4. Press F4 to display the Properties window, and then change the following properties of the UserForm object:

   Property	Change to
   Name	AboutBox
   Caption	About This Workbook

5. Use the Toolbox to add a Label object to the UserForm. If the Toolbox is not visible, choose View ➪ Toolbox.
6. Select the Label object, and in the Properties window change the Name property to lblMessage and enter any text you want in the Caption property.
7. In the Properties window, click the Font property and adjust the font. You can change the typeface, size, and so on. The changes then appear in the form. Figure 41.9 shows an example of a formatted Label control. In this example, the TextAlign property was set to the code that center aligns the text.

   2 - fmTextAlignCenter

   

8. Use the Toolbox and add a CommandButton object to the UserForm. Then use the Properties window to change the following properties for the CommandButton:

   Property	Change to
   Name	OKButton
   Caption	OK
   Default	True

9. Make other adjustments so that the form looks good to you. You can change the size of the form or move or resize the controls.

Testing the UserForm

At this point, the UserForm has all the necessary controls. What's missing is a way to display the UserForm. While you're developing the UserForm, you can press F5 to display it and see how it looks. To close the UserForm, click the Close button (X) in the dialog box title bar.

This section explains how to write a VBA procedure to display the UserForm when Excel is active:

1. Insert a VBA module by choosing Insert ➪ Module.
2. In the empty module, enter the following code:

   Sub ShowAboutBox()
     AboutBox.Show
   End Sub
3. Press Alt+F11 to activate Excel.
4. Choose Developer ➪ Code ➪ Macros (or press Alt+F8). The Macro dialog box appears.
5. Select ShowAboutBox from the list of macros, and then click Run. The UserForm appears.

If you click the OK button, notice that it doesn't close the UserForm as you may expect. This button needs to have an event handler procedure for it to do anything when it's clicked. To dismiss the UserForm, click the Close button (X) in its title bar.

Creating an event handler procedure

An event handler procedure is executed when an event occurs. In this case, you need a procedure to handle the Click event that's generated when the user clicks the OK button.

1. Press Alt+F11 to activate the VBE.
2. Activate the AboutBox UserForm by double-clicking its name in the Project window.
3. Double-click the CommandButton control. The VBE activates the code module for the UserForm and inserts the Sub and End Sub statements for the button's Click event, as shown in Figure 41.10.
4. Insert the following statement before the End Sub statement:

   Unload Me

This statement simply dismisses the UserForm by using the Unload statement. The complete event handler procedure is as follows:

Private Sub OKButton_Click()
  Unload Me
End Sub

After you add the event procedure, clicking the OK button closes the form.

Creating the UserForm

This UserForm needs one piece of information from the user: the type of change to make to the text. Because only one option can be selected, OptionButton controls are appropriate. Start with an empty workbook and follow these steps to create the UserForm:

1. Press Alt+F11 to activate the VBE.
2. In the VBE, choose Insert ➪ UserForm. The VB Editor adds an empty form named UserForm1 and displays the Toolbox.
3. Press F4 to display the Properties window and then change the following properties of the UserForm object:

   Property	Change to
   Name	UChangeCase
   Caption	Change Case

4. Add a CommandButton object to the UserForm and then change the following properties for the CommandButton:

   Property	Change to
   Name	OKButton
   Caption	OK
   Default	True

5. Add another CommandButton object and then change the following properties:

   Property	Change to
   Name	CancelButton
   Caption	Cancel
   Cancel	True

6. Add an OptionButton control and then change the following properties. (This option is the default, so its Value property should be set to True.)

   Property	Change to
   Name	OptionUpper
   Caption	Upper Case
   Value	True

7. Add a second OptionButton control and then change the following properties:

   Property	Change to
   Name	OptionLower
   Caption	Lower Case

8. Add a third OptionButton control and then change the following properties:

   Property	Change to
   Name	OptionProper
   Caption	Proper Case

9. Adjust the size and position of the controls and the form until your UserForm resembles the one shown in Figure 41.11. Make sure that the controls do not overlap.

   

Creating event handler procedures

The next step is to create two event handler procedures: one to handle the Click event for the CancelButton CommandButton and the other to handle the Click event for the OKButton CommandButton. Event handlers for the OptionButton controls are not necessary. The VBA code can determine which of the three OptionButton controls is selected, but it does not need to react when the choice is changed—only when OK or Cancel is clicked.

Event handler procedures are stored in the UserForm code module. To create the procedure to handle the Click event for the CancelButton, follow these steps:

1. Activate the UChangeCase UserForm form by double-clicking its name in the Project window.
2. Double-click the CancelButton control. The VBE activates the code module for the UserForm and inserts an empty procedure.
3. Insert the following statement before the End Sub statement:

   Unload Me

That's all there is to it. The following is a listing of the entire procedure that's attached to the Click event for the CancelButton:

Private Sub CancelButton_Click()
  Unload Me
End Sub

This procedure is executed when the CancelButton is clicked. It consists of a single statement that unloads the form.

Next, add the code to handle the Click event for the OKButton control. Follow these steps:

1. Select OKButton from the drop-down list at the top of the module or reactivate the UserForm and double-click the OKButton control. The VBE creates a new procedure called OKButton_Click.
2. Enter the following code. The VBE has already entered the first and last statements for you:

   Private Sub OKButton_Click()
   ' Exit if a range is not selected
     If TypeName(Selection) <> "Range" Then Exit Sub
    
   ' Upper case
     If Me.OptionUpper.Value Then
       For Each cell In Selection
         If Not cell.HasFormula Then
           cell.Value = StrConv(cell.Value, vbUpperCase)
         End If
       Next cell
     End If
   ' Lower case
     If Me.OptionLower.Value Then
       For Each cell In Selection
         If Not cell.HasFormula Then
           cell.Value = StrConv(cell.Value, vbLowerCase)
         End If
       Next cell
     End If
   ' Proper case
     If Me.OptionProper.Value Then
       For Each cell In Selection
         If Not cell.HasFormula Then
           cell.Value = StrConv(cell.Value, vbProperCase)
         End If
   
       Next cell
     End If
    
     Unload Me
   End Sub

The macro starts by checking the type of selection. If a range is not selected, the procedure ends. The remainder of the procedure consists of three separate blocks. Only one block is executed, determined by which OptionButton is selected. The selected OptionButton has a Value of True. Finally, the UserForm is unloaded (dismissed).

Showing the UserForm

At this point, the UserForm has all the necessary controls and event procedures. All that's left is a way to display the form. This section explains how to write a VBA procedure to display the UserForm:

1. Make sure the VBE window is activated.
2. Insert a module by choosing Insert ➪ Module.
3. In the empty module, enter the following code:

   Sub ShowUserForm()
     UChangeCase.Show
   End Sub
4. Choose Run ➪ Run Sub/UserForm (or press F5). The Excel window is activated, and the new UserForm is displayed, as shown in Figure 41.12.

   

Testing the UserForm

To try the UserForm from Excel, follow these steps:

1. Activate Excel.
2. Enter some text into a range of cells.
3. Select the range with the text.
4. Choose Developer ➪ Code ➪ Macros (or press Alt+F8). The Macro dialog box appears.
5. Select ShowUserForm from the list of macros and then click Run. The UserForm appears, as shown in Figure 41.12.
6. Make your choice and click OK.

Try it with a few more selections, including noncontiguous cells. Notice that if you click Cancel, the UserForm is dismissed and no changes are made.

The code does have a problem, though: if you select one or more entire columns, the procedure processes every cell, which can take a long time. The version of the workbook on the website corrects this problem by working with a subset of the selection that intersects with the workbook's used range.

Making the macro available from a worksheet button

At this point, everything should be working properly. However, you have no quick and easy way to execute the macro. A good way to execute this macro would be from a button on the worksheet. You can use the following steps:

1. Choose Developer ➪ Controls ➪ Insert and click the Button control in the Form Controls group.
2. Click and drag in the worksheet to create the button. The Assign Macro dialog box appears.
3. Select the ShowUserForm macro and then click OK.
4. (Optional) At this point, the button is still selected, so you can change the text to make it more descriptive. You can also right-click the button and choose Edit Text at any time to change the text.

After you perform the preceding steps, clicking the button executes the macro and displays the UserForm.

Making the macro available on your Quick Access Toolbar

If you'd like to use this macro while other workbooks are active, you may want to add a button to your Quick Access Toolbar. Follow these steps:

1. Make sure the workbook containing the macro is open.
2. Right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar from the shortcut menu. The Excel Options dialog box appears, with the Quick Access Toolbar tab selected.
3. Choose Macros from the Choose Commands From drop-down menu on the left. You'll see your macro listed.
4. Select the macro's name and click Add to add the item to the list on the right.
5. (Optional) To change the icon, click Modify and choose a new image; then click OK. You can also change the Display Name.
6. Click OK to close the Excel Options dialog box. The icon appears on your Quick Access Toolbar.

Adding accelerator keys

All Excel dialog boxes work well with a mouse and a keyboard because each control has an associated accelerator key. The user can press Alt plus the accelerator key to work with a specific dialog box control.

Your custom dialog boxes should also have accelerator keys for all controls. You add accelerator keys in the Properties window by entering a character for the Accelerator property.

Your accelerator key can be any letter, number, or punctuation, regardless of whether that character appears in the control's caption. It's a good practice to use a letter that is in the control's caption, though, because that letter will be underlined—a visual cue for the user. (See Figure 41.12 for an example of option buttons with accelerator keys.) Another common convention is to use the first letter of the control's caption. But don't duplicate accelerator keys. If the first letter is already taken, use a different letter, preferably one that is easy to associate with the word (like a hard consonant). If you have duplicate accelerator keys, the accelerator key acts on the next control in the tab order of the UserForm. Then, pressing the accelerator key again takes you to the second control with that accelerator.

Some controls (such as text boxes) don't have a Caption property and other controls (such as labels) can't have the focus. You can assign an accelerator key to a label that describes the control and put that label right before your target control in the tab order. Pressing the accelerator key for a control that can't take the focus activates the next control in the tab order.

Controlling tab order

The previous section refers to a UserForm's tab order. When you're working with a UserForm, pressing Tab and Shift+Tab cycles through the dialog box's controls. When you create a UserForm, you should make sure that the tab order is correct. Usually, it means that tabbing should move through the controls in a logical sequence.

To view or change the tab order in a UserForm, choose View ➪ Tab Order to display the Tab Order dialog box (see Figure 41.13). You can then select a control from the list; use the Move Up and Move Down buttons to change the tab order for the selected control.