Editor's Note: The following MVP Monday post is by Excel MVP Tom Urtis.
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 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.
I’m not recommending to eschew Userforms for dialog sheets, but dialog sheets do have several advantages that merit their worth, for example:
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, 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.
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.
Here’s the code that produces the preceding example. In a standard module:
Public dlgPrint As DialogSheet
Public blnPrint As Boolean
blnPrint = True
Dim ButtonDialog As String
ButtonDialog = "CustomButtons"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Application.DisplayAlerts = True
'Add, name, and hide the custom dialog sheet.
Set dlgPrint = ActiveWorkbook.DialogSheets.Add
.Name = ButtonDialog
.Visible = xlSheetHidden
'Size and caption the dialog frame.
.Height = 130
.Width = 204
.Caption = "Orientation preferences."
'Hide the OK and Cancel default button for custom ones.
.Buttons("Button 2").Visible = False
.Buttons("Button 3").Visible = False
'Add a label at the top of the dialog frame.
.Labels.Add 80, 50, 180, 18
.Labels(1).Caption = "What's your printing preference?"
'Add 3 buttons, Distance from Left, Top; Width and Height.
.Buttons.Add 84, 84, 80, 18 'Custom Button #1,index #3.
.Caption = "Print Portrait"
.OnAction = "myCustomButton"
.Buttons.Add 180, 84, 80, 18 'Custom Button #2,index #4.
.Caption = "Print Landscape"
.Buttons.Add 84, 116, 176, 18 'Custom Button #3, index #5.
.Caption = "Forget it -- I don't want to print anything."
Application.ScreenUpdating = True
'The X Cancel button was clicked on the title bar.
If .Show = False Then
MsgBox "You clicked the ''X'' close button.", 64, "Print cancelled."
blnPrint = False
'Close the dialog sheet's With structure.
'Delete the dialog frame.
Private Sub myCustomButton()
'Hide the custom dialog sheet.
'Cases for index of custom button that was clicked.
Select Case dlgPrint.Buttons(Application.Caller).Index
Case 3: ActiveSheet.PageSetup.Orientation = xlPortrait 'Portrait.
Case 4: ActiveSheet.PageSetup.Orientation = xlLandscape 'Landscape.
Case 5: blnPrint = False 'Cancel (the "Forget it"-captioned button).
MsgBox "No problem -- nothing will print.", 64, "Print cancelled."
Private Sub DeleteDialog()
'Delete a previous dialog sheet if by chance it exists.
.ScreenUpdating = False
.DisplayAlerts = False
.DisplayAlerts = True
.ScreenUpdating = True
As a precaution, when the workbook is activated or deactivated, I delete a previously-created dialog sheet if by chance one still exists. This can happen after a critical moment such as a power failure or other odd happenstance. For this example, the BeforePrint event triggers the dialog, with the following code in the workbook module.
Private Sub Workbook_Open()
Private Sub Workbook_Activate()
Private Sub Workbook_Deactivate()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If blnPrint = False Then Cancel = True
For more information, a sample workbook can be viewed on my site
Tom Urtis is a Microsoft Office developer, programmer, trainer, and author, with 30 years of experience in business management and developing spreadsheet and database projects for business of all types.
Tom founded Atlas Programming Management in 2000 (www.atlaspm.com), a silicon valley-based Office business solutions company specializing in Excel that provides consulting, development, training, and support of fully customized projects for a diverse international clientele.
Tom is an Excel instructor, and received the Most Valuable Professional award for Excel from Microsoft in 2008. He is the author of "Excel VBA 24-Hour Trainer", a 400-page book and CD package, and co-author of "Holy Macro! It's 2,500 Excel VBA Examples". Tom is a technical editor and consultant for Excel books and training material, and an active contributor to Excel newsgroups.
Tom is a graduate of Michigan State University, living in the San Francisco Bay Area. He can be reached by email at firstname.lastname@example.org.
The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager for Dynamics, Excel, Office 365, Platforms and SharePoint in the United States. She has been working with MVPs since her early days as Microsoft Exchange Support Engineer when MVPs would answer all the questions in the old newsgroups before she could get to them.
Och Dialogs. I forgot that something like this exists. Thanks for that. Tear in the eye is turning.