Over the course of Excel’s evolution, many older features that were state of the art in their day have been cast aside for newer ways of doing things. Some of those older features remain fully supported and useful in later versions of Excel.
One such oldie but goodie is the 5.0 Dialog sheet, the precursor to UserForms for building user interfaces in Excel 5 and Excel 95. The dialog sheet has become a lost art in this modern era of UserForms and programmable ActiveX controls, but that’s what makes it special when used in the appropriate development scenario.
Dialog sheet examples
Dialog sheets create customized dialog boxes on the fly using Forms controls. After a dialog sheet serves its purpose, it is deleted as part of the VBA code that created it. Here are a few dialog boxes I created using dialog sheets.
Advantages of dialog sheets
I’m not recommending to eschew Userforms for dialog sheets, but dialog sheets do have several advantages that merit their worth, for example:
• Dialog sheets utilize only Forms controls which, unlike ActiveX controls, are fully integrated with Excel and do not cause as many VBA programming errors.
• Dialog sheets are a history lesson in Excel. You may come across older workbooks with dialog sheets, so it’s a good idea to at least be familiar with them as you would any Excel object.
• A frequent question in Excel newsgroups is how to customize the button captions on Message Boxes. With a dialog sheet, a solution can be posted that visually satisfies this request, without requiring the project’s author to know anything about UserForms or setting trusted access to anyone’s VBE.
• They have an intangible “wow” factor of a custom-looking Message Box or dialog box that has a simple, straightforward design.
• They are fun to work with, as a way to do something different that can also get the job done.
Disadvantages of dialog sheets
To be fair, a downside to dialog sheets is the volume of code they require for being produced, designed, executed, and discarded. It’s the trade-off from not having to manually create a UserForm, and draw controls onto the form, and associate the event code with the controls. With dialog sheets, the creation and positioning of controls and their OnAction code are written one time, albeit with a fair amount of VBA code.
A dialog sheet will never win a beauty contest. One look at a dialog sheet is enough reason to avoid showing it. When I use them, only the dialog box (called the DialogFrame) is seen by the user, not the dialog sheet itself.
To see a dialog sheet, as shown in the next pictures, right-click any worksheet tab. From the pop-up menu, select Insert, and on the General tab of the Insert dialog box, select MS Excel 5.0 Dialog.
Here’s what a new dialog sheet typically looks like.
A Working Example: Customizing Buttons for Printing Options
To enhance the user’s experience when printing a worksheet, you can show options to select a print orientation of Landscape or Portrait, or to cancel the print job altogether. As shown in the next pictures, when the Print button is selected, a simple dialog lets the user decide how or if the print job will be carried out.
If the Close or “Forget it…” buttons are selected, a Message Box confirms the print job’s cancellation.
The code that produces this example is in this downloadable workbook.