Customizing Your Messages With Dialog Sheets

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 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, 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.

Here’s the code that produces the preceding example. In a standard module:

Public dlgPrint As DialogSheet

Public blnPrint As Boolean

Sub CustomButtonsDialog()

blnPrint = True

Dim ButtonDialog As String

ButtonDialog = "CustomButtons"

Application.ScreenUpdating = False

Application.DisplayAlerts = False

On Error Resume Next

ActiveWorkbook.DialogSheets(ButtonDialog).Delete

Err.Clear

Application.DisplayAlerts = True

'Add, name, and hide the custom dialog sheet.

Set dlgPrint = ActiveWorkbook.DialogSheets.Add

With dlgPrint

.Name = ButtonDialog

.Visible = xlSheetHidden

'Size and caption the dialog frame.

With .DialogFrame

.Height = 130

.Width = 204

.Caption = "Orientation preferences."

End With

  '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.

With .Buttons(3)

.Caption = "Print Portrait"

.OnAction = "myCustomButton"

End With

.Buttons.Add 180, 84, 80, 18 'Custom Button #2,index #4.

With .Buttons(4)

.Caption = "Print Landscape"

.OnAction = "myCustomButton"

End With

.Buttons.Add 84, 116, 176, 18 'Custom Button #3, index #5.

With .Buttons(5)

.Caption = "Forget it -- I don't want to print anything."

.OnAction = "myCustomButton"

End With

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

End If

'Close the dialog sheet's With structure.

End With

'Delete the dialog frame.

Run "DeleteDialog"

End Sub

 Private Sub myCustomButton()

'Hide the custom dialog sheet.

dlgPrint.Hide

'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."

End Select

End Sub

Private Sub DeleteDialog()

'Delete a previous dialog sheet if by chance it exists.

With Application

.ScreenUpdating = False

.DisplayAlerts = False

On Error Resume Next

DialogSheets("CustomButtons").Delete

Err.Clear

.DisplayAlerts = True

.ScreenUpdating = True

End With

End Sub

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()

Run "DeleteDialog"

End Sub

Private Sub Workbook_Activate()

Run "DeleteDialog"

End Sub

Private Sub Workbook_Deactivate()

Run "DeleteDialog"

blnPrint = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Run "DeleteDialog"

blnPrint = True

ThisWorkbook.Save

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Run "CustomButtonsDialog"

If blnPrint = False Then Cancel = True

Run "DeleteDialog"

End Sub

For more information, a sample workbook can be viewed on my site

Author's Bio

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 tom@atlaspm.com.

MVP Mondays

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.