Excel VBA: creating custom dialog (user form)

Tags: Word art in dialog sheet, Add word art to dialog sheet, Add shape to dialog sheet, Shape in dialog sheet, Rich text in dialog sheet, Add rich text in dialog sheet, Equation in dialog sheet, Add equation to dialog sheet, Add picture to dialog sheet, Picture in dialog sheet.


I am working with VBA user-forms for long long time and they unfortunately have ugly classic interface as you can see the user-form below). I also tried to solve this problem using Visual Studio, but I couldn't achieve that as I am not a professional programmer :)

I was always wondering how to create user-forms in MS Excel with nice looking controls till I discovered by accident what is called "Dialog sheets". In dialog sheet you can create a user-form assigned to workbook and add your own controls and assign macros to them. Here under, few quick steps to get it done in few minutes:

Step 1: Creation

1. Create an new workbook (or open an existing one). Right-click on any sheet tab to get a menu like in the image below

2. Click "Insert..." button, then you will get a dialog like the the one shown below

3. Select "MS Excel 5.0 Dialog" and press "OK" button. Then you will get a new sheet as shown below which has default name "Dialog1"

Step 2: Modification

1. Open the "Developer" tab, then press "Insert" button to be able to add "Form controls"

2. You can add and resize controls to get user-form like the one shown below

Step 3: Assign macros to controls

You can right-click on any control and select "Assign macro..." from the popup list.

Step 4: Calling (displaying) dialog

As an example, you can insert a "Command button" in :"Sheet1" and assign the following single-line macro to it which will display your dialog

Now you can click on the button "Display my dialog" and display the custom dialog (user-from) like magic!!!

Conclusion:

For me, dialog sheet form is very pretty, nice look, and visually-pleasant because its controls have the native theme of the operating system (Windows 7 in my case) Windows Vista or Windows XP, but it does not support the following controls which already available in VBA user-forms:

- Tab/notebook/multi-page control to display multiple pages
- Slider control
- Frame (with sibling controls)

One of the great advantages of dialog sheets that you can insert "Objects" in them like:

- ٍShapes!!! - Image
- Visio object (drawing)
- Microsoft equation 
- Word file
- etc

The following user form created by dialog sheet contains some interesting features (guess them out)...

The following is a trick, but it is a little bit useless. You can add a dialog inside another dialog by drag copying (not by copying and pasting) the dialog sheet.

Enjoy...