Excel Developer Tip

Return to The Spreadsheet Page

Excel page

Tip archives

General UserForm Tips

One of the most useful features in Excel is the ability to create custom dialog boxes (UserForms). Before embarking on this task, ask yourself if a custom dialog box is really necessary. In some cases you may be able to use one of the following options to get user input:

  • VBA's MsgBox function
  • VBA's InputBox function (always returns a string)
  • Excel's InputBox function (you can specify the type of data returned)
  • Excel's GetOpenFilename method
  • Excel's GetSaveAsFilename method
  • Excel's FileDialog object (Excel 2002 only)

And, don't forget that your VBA code can display most of Excel's built-in dialog boxes. For example, the following subroutine displays the Move or Copy dialog (used to move or copy a sheet): 

Sub ShowMoveCopy()
    Application.Dialogs(xlDialogWorkbookMove).Show
End Sub

When designing your userForm, a good rule of thumb is to make the dialog boxes look and work like those that are built into Excel. If you're a beginner, you may find it instructive to try to recreate a particular Excel dialog box. You may find a few situations in which a built-in dialog box can't be recreated exactly.

Checklist

Before you unleash your UserForm, give it one final check using the following checklist.

  1. Are the controls aligned with each other?
  2. Are similar controls the same size?
  3. Are controls evenly spaced?
  4. Is the dialog box too overwhelming? If so, you may want to use a series of dialogs (like a Wizard), or use a MultiPage control.
  5. Can every control be accessed with an accelerator key?
  6. Are any of the accelerator keys duplicated?
  7. Are the controls grouped logically (by function)?
  8. Is the tab order set correctly? The user should be able to tab through the dialog box and access the controls sequentially.
  9. If the UserForm will be stored in an add-in, did you test it thoroughly after creating the add-in? It's important to remember that an add-in will never be the active workbook.
  10. Will your VBA code take appropriate action if the UserForm is canceled, or if the user presses Esc?
  11. Are there any misspellings in the text? Excel's spelling checker doesn't work with UserForms.
  12. Does the UserForm have an appropriate caption?
  13. If applicable, will your UserForm fit on the screen in 640 X 480 mode?
  14. If your UserForm will be used in multiple versions of Excel, have you tested the application on all versions?
  15. If you use help, is the help topic correct? And does it explain all the controls?