Dialog Sheets

Customizing Your Messages With Dialog Sheets

Download the example workbook.

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.


22 comments on “Dialog Sheets
  1. Mjohn Dmz says:

    Another advantage is I can create dialog at run-time without access to the excel object model (which requires admin rights).

  2. Niels Wulff says:

    Dialog sheet are not working in the new office 2016 update for mac.

  3. Alex M says:

    how to activate ActiveX Control on dialog box MS Excel 5.0

    • Tom Urtis says:

      Details please. What dialog box, and why would you try to put, for example, a command button on a Find & Replace dialog box? Maybe you just need to create a UserForm.

  4. Pierre GYSELINX says:

    Hi,

    I still use such a dialogsheet 🙂
    How can I customize the position of the dialog box on the screen ?

    • Tom Urtis says:

      Hello Pierre – –

      I have done this for userforms, pictures, and message boxes (examples on my blog), but I haven’t done it for dialog sheets (yet). I am in the middle of a few projects but if you search my blog for keyword position you’ll see what I did that maybe you can use. Otherwise, I can look at this next week if you don’t get an answer beforehand. You may try an excellent popular Excel forum to which I also contribute that can answer your question quicker, at mrexcel.com.

  5. Doug says:

    Thought this was for MAC users. Not so. It is useless for us.

    • Tom Urtis says:

      Yep. Some of what I publish Macs can use, but because of the way Excel is and Macs are, Macs don’t support programming code and a few other features. I wish it were otherwise.

      • Brad says:

        I have a large Macro (PC not Mac) I wrote years ago that uses DialogSheets to enter data into multiple spreadsheets. It worked fine until yesterday when I got the following error message:
        Run-Time Error ‘1004’:
        Show method of DialogSheet class failed

        The step it fails on is:
        DialogSheets(“…..”).Show

        This command now fails in every instance it’s used in the Macro no matter which DialogSheets its trying to show. Do you know what caused the command to stop working now? Is there a work around or command that will show the DialogSheets? I really don’t want to rewrite my VBA basic file & create User forms.

        • Tom Urtis says:

          Just wondering about the “wrote years ago” bit…
          What version or subscription release of Excel are you using?
          Dialog sheets are fully supported in versions at least up to 2016.
          I wonder if you are using 365 or 2019.

          • Brad says:

            Excel 2016 MSO (16.0.13530.20054) 32 bit. I checked my Excel update history and the last update was a month ago and the macro was working after that update but for some reason it stopped working last week so I am confused what changed to cause it to stop working.

            • Tom Urtis says:

              No idea. The fact that it was working up until last week, and you are on 2016 which fully supports dialog sheets, suggests to me that something else is going on. It might be a sheet name change you made in the code, just guessing, but it works for me and I cannot duplicate your error.

              • Dann says:

                Hi Tom,

                We have a similar problem. Same error code.

                The script was written pre-2000 by an employee that is no longer with the company. We have updated the data on the sheets in the workbook a few times over the years. There have not been any issues with getting the macro to run until the last couple of days.

                Now we get the very same error that Brad described above. There have not been any changes to the workbook since 2018.

                The dialog is a very simple item selection box.

                ——
                Sub ShowSelectMountDialog()
                DialogSheets(“Select Mount Dialog”).Show
                End Sub
                ——

                When I look at the object editor, I don’t even see a “DialogSheets” object.

                We are running MSExcel 2016 (16.0.13530.20054) 32 bit.

                Apologies if any of this seems like I’m asking the wrong question. I haven’t done any coding in decades. Just trying to get this one tool back to working order.

                • Tom Urtis says:

                  You are asking the right question to the right person because I wrote the code, so thanks for asking.

                  I am interested to see what is happening. Can someone please send me the workbook (or a mock copy of it if there is sensitive data involved) so I can see what is going on.

  6. Brad says:

    Thanks. I didn’t change anything that’s what’s so frustrating other than entering data. Do you know if there is another command (or syntax) that will display a dialog sheet?

    • Tom Urtis says:

      Hello, Brad, replying back to you as I did to Dann, I am interested to see what is happening. Can you please send me the workbook (or a mock copy of it if there is sensitive data involved) so I can see what is going on. Thanks.

      • Brad says:

        I tried the sheet on a different computer loaded with MSExcel 2016 (16.0.5083.1000) MSO (16.0.5080.1000) 32 bit and the command worked so it appears to be an issue with my Excel version. I went in and ran the repair options for Office on my computer and the command still doesn’t work. Looking at my Widows update history, my system did a Quality update (2021-01, KB4598242) on 1/13/21 so I’m wondering if that update may have impacted it. I’m wondering if Dann is running Excel on the same update as me and if his issue only started after it was installed last week. When you tried the show command, did you try it on the new update?

        Unfortunately, my workbook has sensitive information built into the Macro since it links all my financial spreadsheets.

        • Tom Urtis says:

          Thanks a lot for that update, Brad, and for the KB number.
          Looking forward to Dann’s reply if he has — or has not — done the same update.

          • Brad says:

            Wondering what Windows update you tried the command on when it worked & if you’ve since done the latest update and tried it again. I’m trying to decide if I want to uninstall the update but don’t want to go through the hassle if it won’t help.

Leave a Reply

Your email address will not be published. Required fields are marked *

*