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.


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

            • Brad says:

              Just to let you know that I just did the latest window updates & the DialogSheets(“…..”).Show command works again

              • Tom Urtis says:

                That is great news, thanks for letting me know!!

                • Brad says:

                  Just to let you know that the latest Windows update disabled the DialogSheets Show command & its worse this time in that it causes Excel to crash.

                  • Tom Urtis says:

                    I just tested it again and it worked fine for me, no problems. It would help if you explain what version or subscription release of Excel you are using, if you are using 32 or 64 bit, and what Windows version you are on. Not all Excel systems run the same way, so something else is going on with your set-up that is not happening on mine or others/

  7. Wolfgang says:

    Hi,
    my last windows-upd. is KB5000802 and my dialogsheet is still not running !

    • Tom Urtis says:

      It runs for me but as you can see from my previous replies to comments on this thread, just saying that some code is not running tells me nothing.

      Does ANYTHING happen, even an error.
      What is your Excel version or release.
      What is your Excel type — 32 or 62 bit.
      What is your device — PC or Mac.
      What is your Windows version.
      What is your System OS type – – 32 0r 64 bit.

      A lot of software and system variations out there, so for me to understand better, especially when something works for me and not for you, please explain what you are working with. Thanks.

  8. Wolfgang says:

    Hi Tom,
    first of all: I use it only 1x time a year and last year everything ran well !
    … and I try to tell in correct IT-English 😉
    1) “runtime-failure ‘-2147417848 (800101008)’: ‘Show’ for ‘DialogSheet’ fails”
    2) Excel 2016 (32bit) 3) PC
    4) Windows 10 Home (64bit) Vers.20H2 (19042.867) – last Upd.: KB5000802 / KB4589212

    • Tom Urtis says:

      I cannot duplicate your experience. Your system specs are the same as mine. It would b easy to point to the KB updates as the culprit, and maybe they are but I cannot say for sure because everything works on my system.

      Maybe someone reading this can chime in, if they have made the same KB updates. Otherwise, depending on if you are on an employer’s network or some other centralized LAN or WAN, and your Excel workbook is NOT on your C drive, it might be a networking conflict with restrictions you are unaware of.

      If you want to email me the workbook I can see if the code runs for me here. Beyond that, I just cannot say.

      • Brad says:

        My KB update was the same as Wolfgang. Like my earlier experience that I reported, my macro worked fine the day before the update and after the update it started having problems. The problems are different this time in that each of the dialogue sheets I use have different issues. Some still work but the just load data improperly into the dialogue sheet but one of the dialogue sheets will now actually lock up excel so that I cannot exit excel without going to task manager and exit it. I have run the same file on a different PC that has a different windows config & everything works fine on that machine. I have also pulled an older version of the file and it has the same response on the two PCs so I’m confident it’s not a corrupted file issue. I manually stepped thru the Macro sequence and the steps are executing correctly until it gets to the dialogue sheets command at which point the errors show up (e.g. excel locks up or the data that is displayed does not match the value loaded earlier into the variable).

        When you say your system specs are the same, you never actually say what Windows update you are on. Since my issue initially started after an update & then went away after the next update and has now recurred with the March update, I am 100% confident it is related to the Widows updates.

        • Brad says:

          I went back to my update history. The following updates were loaded on March 5 just before my dialogue sheets issues started recurring:

          February 24, 2021—KB4601382 (OS Builds 19041.844 and 19042.844) Preview

          February 24, 2021-KB4601554 Cumulative Update Preview for .NET Framework 3.5 and 4.8 for Windows 10, version 2004, Windows Server, version 2004, Windows 10, version 20H2, and Windows Server, version 20H2

          KB4577586: Update for the removal of Adobe Flash Player: October 27, 2020

          I have loaded a couple of windows updates since then including the KB5000802 and the issue still occurs.

  9. Wolfgang says:

    Hi Brad,
    I also got the KB…1554, but no offer for KB…1382; otherwise we (in Germany 😉 got in addition KB890830 + KB4589212 (both in march)
    Hi Tom, ad “email me the workbook”
    shall I send it to ‘tom@atlaspm.com’ ?
    and which Version the original “xlt” or converted “xltm” ?

  10. Wolfgang says:

    Hi Brad,
    additional: also in February: KB4601050 + KB4023057; in March: KB5000802

    • Brad says:

      I also did the 9212 & 1567 updates later in March along with the 802 update and they did not fix the issue. When I had DialogSheet issues in January, the February update restored the function so there is some hope that the April update will help.

  11. Tom Urtis says:

    Thank you, everyone for your comments and observations. I have not made the same KB updates that I see others here have, and that looks to be the reason why the dialog sheets work for me, and used to work for you but no longer do. I will research which update(s) is/are the culprits. What I am afraid of is that dialog sheets have long ago been replaced by UserForms, and Microsoft has not supported them for more than 20 years.

    “Not supporting” does not mean dialog sheets do not work in most systems. It just means that Microsoft encourages these KB updates to address modern day systems, versions, and releases, without caring about what negative side effects happen to outdated features such as dialog sheets.

    Wolfgang:
    Yes, that email address is the one to use. You can send me either version and here I will convert it to xlsm. However, I expect that the dialog sheet code will work for me no problem, because I have not made the same updates on my computers here that you did. And, I just tested my code here today and the code works great.

    Thanks again to everyone. Your comments and replies to me and to each other have revealed the negative effect of some KB updates to dialog sheets that I was not aware of. I will try some workaround code to see if dialog sheets, which I know are outdated but still have become a lost art and have usefulness, can still operate despite the offending updates.

    • Brad says:

      Thanks for your insight. Since it looks like I’m going to have to rewrite my code and start using UserForms, is there a website that you’d recommend as a tutorial?

      • Tom Urtis says:

        Learning about UserForms in my opinion is better done with a book than from a website, because of all the possible combinations of controls and programming code conventions.

        If you are more of a visual person than a book person, try the keywords Excel UserForms on YouTube but be aware that just like anything else, people claim to be experts at things they really are not expert at, so you will not learn everything you need to know from one source.

        I am biased but really I am not trying to promote my latest VBA book, just saying I devoted 3 chapters on UserForms where I fully explain from start to finish how to create them, with downloadable examples and videos, at amazon.com/Excel-VBA-24-Hour-Trainer-Urtis/dp/1118991370/. There are other resources too, just pick whichever medium works best for you, and if the person doing the explaining makes sense to you.

  12. Brad says:

    After further looking at the timeline of my updates & dialogsheet issues, it seems to be tied to whenever I get an update associated with Cumulative Update Preview for .NET Framework 3.5 and 4.8 for Windows 10, version 20H2 for X64. The issue has either started or was fixed after I did one of these .NET updates. Hope this helps if anyone is trying to troubleshoot why the updates are causing a problem.

  13. user from Germany says:

    We have same problem while using a macro created in 1999 with one DialogSheet.

    In March the Office update from Version 2101 to Version 2102 moved a ListBox to the back, so there was nothing to select any longer.
    We moved the ListBox to front again.

    On April 06 I restarted my system for further updates installation after being reminded several times.
    Yesterday the macro stopped with run-time error 800 10 108 (Method Show of object DialogSheet failed). I have to use TaskManager to quit Excel.
    By now I am the only user affected by this error.

    Today, just to crosscheck, I created a new workbook, inserted a DialogSheet, called ThisWorkbook.DialogSheets(1).Show from a macro and it is displayed without any problems …
    The old macro still stops at *.Show.

    Thanks a lot for sharing your experience and knowledge.

  14. user from Germany says:

    Solution found by accident:

    Today I had to use a different workplace of a user with office language preferences “Match Microsoft Windows (German)”.
    The macro performed without any error.

    Afterwards I changed the office language preferences at my systems to German too.
    The macro performed without any error.

    I have been using English since starting for my company in 1993 …

Leave a Reply to user from Germany Cancel reply

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

*