How to Make Your Own Excel Dialog Box with a Visual Basic UserForm

The built-in dialog boxes, MsgBox and InputBox$, are very useful for getting input from the user.  But sooner or later you will want to go further.  You might, while running code, want the user to make a choice from a list, or between several options.  For this, you want a UserForm.  For example:

The program should then respond to the user’s choice.

1.       Exercise: Create a UserForm:

This example is in the file Macros.xlsm, which you can get free of charge from us.  Or you can copy the code below into a blank worksheet and then run it.

Here is the setup:

Create this in the Visual Basic Editor as follows:

• View the Project Explorer (Ctrl+R)
• Right-click in the Project Explorer and Insert > UserForm
• View Properties (F4)
• Change the “(Name)” property of the form to frmDemoForm
• Change its Caption to the heading you want.
• The Toolbox should be visible (if not, View > Toolbox)
• Click the list box and drag a suitable size list box on the UserForm
• Change its “(Name)” property to lstHeights.  Change the Font if you wish.
• Add a Command Button to the form, name it cmdOK and with the caption OK.
• Add another Command Button, cmdCancel, with the caption Cancel.
• Double-click the OK button to go to the code window.  Add the required code.  The code for both buttons should end with the statement End, which closes the form.
• In the case of our demonstration, we need a button on the worksheet to open the form.  The code for this is frmDemoForm.Show
• Alternatively, the dialog box could, for example, run when the file opens. Then you would put the code in the object ThisWorkbook, in Sub Workbook_Open.

2.        More Code

Here’s the code that is not visible in the screen capture above (of course, you can make up your own):

Private Sub cmdOK_Click()         ' OK Button
  ' Set Message based on selection:
  Select Case lstHeights.ListIndex
  Case 0    ' "Tall"
    Msg$ = "Hi Beanpole!"
  Case 1    ' "Average"
    Msg$ = "How boring to be just ""Average""!"
  Case 2    ' "Short"
    Msg$ = "Hi Shorty!"
  Case 3    ' "Dwarf"
    Msg$ = "Dwarves have a long and glorious history of contributions to Mankind"
    Msg$ = Msg$ & vbLf & "from Snow White, through The Lord of The Rings, to Terry Pratchett."
    Msg$ = Msg$ & vbLf       ‘ Blank Line
    Msg$ = Msg$ & vbLf & "Therefore, never be ashamed of your height."
  End Select
 
  ' Display message:
  MsgBox Msg$, vbInformation, "A personal message for you"
 
  End     ' Close the dialog box
End Sub

3.       Working with a UserForm:

To select more than one control, start at a position not touching any of the controls.  Drag a box that touches or includes all the controls you want to select.

Otherwise, click on the first control you want to select.  Then hold a Shift key while clicking on the other controls you want to select.

If you selected more than one control, you can move the selection as a unit.  You can also. drag the handles to re-size all the controls together (by the same amount)

To re-size the Form itself, select it first.  Then grab and drag a corner or an edge handle.