A Dialog Box Checklist for Excel 2016 VBA - dummies

A Dialog Box Checklist for Excel 2016 VBA

By John Walkenbach

If you’re a VBA programmer, chances are you will be making lots of dialog boxes for Excel 2016. If so, here is a handy-dandy checklist for use when creating dialog boxes:

  • Are the controls aligned with one another?

  • Are similar controls the same size?

  • Are controls evenly spaced?

  • Does the dialog box have an appropriate caption?

  • Is the dialog box overwhelming? If so, you may want to use a series of dialog boxes or divide them over a MultiPage control.

  • Can the user access every control with an accelerator key?

  • Are any accelerator keys duplicated?

  • Are the controls grouped logically, by function?

  • Is the tab order set correctly? The user should be able to tab through the dialog box and access the controls sequentially.

  • If you plan to store the dialog box in an add-in, did you test it thoroughly after creating the add-in?

  • Will your VBA code take appropriate action if the user cancels the dialog box, presses Esc, or uses the Close button?

  • Does the text contain any misspellings? Unfortunately, the Excel spell checker doesn’t work with UserForms, so you’re on your own when it comes to spelling.

  • Will your dialog box fit on the screen in the lowest resolution to be used (usually, 1024×768 mode)? In other words, if you develop your dialog box by using a high-resolution video mode, your dialog box may be too big to fit on a lower-resolution screen.

  • Do all TextBox controls have the appropriate validation setting?

  • If you intend to use the WordWrap property, is the MultiLine property also set to True?

  • Do all ScrollBars and SpinButtons allow valid values only?

  • Do all ListBoxes have their MultiSelect property set properly?

The best way to master custom dialog boxes is to create dialog boxes — lots of them. Start simply and experiment with the controls and their properties. And don’t forget about the Help system; it’s your best source for details about every control and property.