Creating List Dialog Boxes

When you start creating Visual Basic projects and features for Office, you'll often come across scenarios in which you'll want to position your ActiveX controls on your UserForm to resemble the dialog boxes in Microsoft Access, Word, Excel, PowerPoint, or Outlook. You'll also find many dialog boxes in Word, Excel, and PowerPoint that share a set of controls and a layout, although the tasks of the dialog boxes may be very different.

Move Items Between Two List Boxes

A common dialog box layout is to have two ListBox controls with Add, Remove, and Remove All command button controls in between. You see this layout when you're asked to select items from one full list in order to create a subset of items. The Define Custom Show dialog box in PowerPoint is an example of this layout, which you'll create below in Excel.

  1. Without exiting Excel, start PowerPoint. In the opening PowerPoint dia-log box, select Blank Presentation and click OK. In the New Slide dialog box, select any AutoLayout and click OK.

  2. On the Slide Show menu, click Custom Shows, and click the New button. You're going to create a dialog box in Excel that's similar to the Define Custom Show dialog box.

  3. Click Cancel, click Close, and exit PowerPoint without saving changes.

  4. In the Visual Basic Editor of Excel, click UserForm on the Insert menu.

  5. In the Toolbox, click the ListBox control and drag the control to the left side of the UserForm.

  6. Click the ListBox control again and drag the control to the right side of the UserForm.

  7. Move and size the controls using the following illustration as a guide:

  8. Add two CommandButton controls, placing the first control between the two list boxes and the second one just below the first.

  9. Add two more CommandButton controls, placing them below the second list box, ListBox2. (The fourth command button is to the right of the third.) Use the following illustration as a guide:

  10. In the Properties window, set the following values. You may need to resize the window to see the complete control names.

Control Property Value
CommandButton1 Name cmdAdd
CommandButton1 Caption Add >
CommandButton2 Name cmdRemove
CommandButton2 Caption < Remove
CommandButton3 Name cmdOK
CommandButton3 Caption OK
CommandButton4 Name cmdCancel
CommandButton4 Cancel True
CommandButton4 Caption Cancel
ListBox1 Name lstEmployees
ListBox2 Name lstAttendees

TIP
When working with controls in Visual Basic code, you commonly add a three-letter prefix to the names of the ActiveX controls. The prefix indicates what type of control is being referenced in code. For the controls listed in the Toolbox of the Visual Basic Editor, use the following prefixes: chk for check box, cbo for combo box, cmd for command button, img for image, lbl for label, lst for list box, mpg for multipage, opt for option button, scb for scroll bar, spn for spin button, txt for textbox, and tgl for toggle button.

    To enter property values more efficiently, click the control on the UserForm before typing its values in the Properties window.

If your UserForm is behind the Code window, display it by clicking UserForm3 (UserForm) on the Window menu.

  1. Double-click the cmdAdd command button and type the following statements in the Code window between the Private Sub cmdAdd_Click and End Sub statements:

    2. Dim i As Integer
For i = 0 To lstEmployees.ListCount _ 1
    If lstEmployees.Selected(i) = True Then
        lstAttendees.AddItem _
            lstEmployees.List(i)
        lstEmployees.RemoveItem i
        lstEmployees.Selected(i) = False
    End If
Next i

    The For…Next loop, discussed in the previous chapter, iterates through each item in the lstEmployees list box and checks to see whether the item is selected. If it is, it's added to the lstAttendees list box and removed from the lstEmployees list box. The Selected property of the lstEmployees list box control returns a Boolean value indicating whether the item is selected.

  2. Double-click the cmdRemove command button and type the following program statements in the Code window between the Private Sub cmdRemove_Click and End Sub statements:

    3. Dim i As Integer
For i = 0 To lstAttendees.ListCount _ 1
    If lstAttendees.Selected(i) = True Then
        lstEmployees.AddItem lstAttendees.List(i)
        lstAttendees.RemoveItem i
    End If
Next i

    The structure and sequence of this code segment is exactly the same as the code assigned to the cmdAdd command button, but it moves items in the opposite direction (that is, from the lstAttendees list box to the lstEmployees list box).

  3. Double-click any blank area on the UserForm, select Initialize from the Procedure drop-down list in the Code window (the list on the right), and type the following program statements in the Code window between the Private Sub UserForm_Initialize and End Sub statements:

    4. With lstEmployees
    .AddItem "Dave"
    .AddItem "Rob"
    .AddItem "Greg"
    .AddItem "Christina"
    .AddItem "Mark"
End With

    When you add the Employees list box to the UserForm, it doesn't contain any of the items in the list. The With…End block adds five items to fill the list box when the UserForm is first initialized, just before it's displayed on the screen. In an actual program, the program would probably load the data from a file stored on disk.

  4. Double-click the cmdOK and cmdCancel command buttons and type Unload Me between the Sub and End Sub statements.

    5. Now you're ready to go.

  5. Run the dialog box by pressingF5, and click each button to reveal its functionality.

    6. When you click the Add button, the program removes selected items in the left list box and adds them to the right list box. The opposite happens when you click the Remove button.

  6. Click the Save button to save your work.


    7. Save button