XL: How to Create a Dynamic Dialog Box

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

The following example explains how to create a dynamic dialog box that changes size to show more options when you click a button on the dialog box.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/refguide/

Creating the Dialog Box

1. In a new workbook, on the Insert menu, click Macro, and then Dialog.



2. On the Forms toolbar, click the Option Button button. The mouse pointer will change to a cross. Center the pointer one-half inch below the bottom of Dialog1. Press and hold down the left mouse button and drag down a quarter of an inch and over one-and-a-half inches. Release the mouse button. There should now be an option button below Dialog1.



3. On the Forms toolbar click the Create Button button. Inside of Dialog1, center the pointer one-and-a-half inches from the bottom and one and a half inches from the right side of the dialog box. Drag down three-quarters of an inch and over to the right three-quarters of an inch. Release the mouse button. There should now be a rectangular button on Dialog1.

Creating the Code

1. On the Insert menu, click Macro, and then Module.



2. Type the following code:




      Sub Show_Dialog()
      DialogSheets(1).Show
      DialogSheets(1).DialogFrame.Height = 147
      End Sub

      Sub Expand()
      DialogSheets(1).DialogFrame.Height = 200
      End Sub 

Attaching the Expand Procedure to the Dialog Button

1. Activate the Dialog1 sheet by clicking its sheet tab.



2. Center the mouse pointer over the top of the button that you created in the bottom-right corner of Dialog1. Click once with the right mouse button. Click Assign Macro on the shortcut menu, and from the list of available macros, select Expand. Click OK.

Running the Macro

1. Activate a worksheet within the workbook. On the Forms toolbar, click the Create Button button and create a button on the current worksheet. The Assign Macro dialog box will appear. Select Show_Dialog, and then click OK.



2. Click with the left mouse button once in a blank area of the current worksheet.



3. Click with the left mouse button once on the button to run the macro.



4. When the dialog box appears, click with the left mouse button once on the button that you created to show the option button. Click OK to dismiss the dialog box.

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"

For more information about dialog boxes in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   Dialog boxes 

   Dialog boxes 

Additional query words: 5.00a 5.00c

Keywords          : kbprg kbhowto 
Version           : WINDOWS:5.0,5.0c,7.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: June 14, 1999