Custom Dialog Boxes

In Microsoft Excel 5/95, custom dialog boxes were created by adding dialog sheets to a workbook. In Microsoft Excel 97, custom dialog boxes are created with UserForms added to a project in the VBE. Controls are added to UserForms with the Toolbox, in a manner very similar to the way controls were added to Microsoft Excel 5/95 DialogSheets. The most visible difference between UserForms and DialogSheets is that new DialogSheets always had OK and Cancel buttons included on them by default, whereas new UserForms begin as a blank slate. You must add and create code for all controls.

The new ActiveX controls provided with Microsoft Excel 97 and Office 97 are greatly enhanced compared to the simple controls available in earlier versions. You now have much finer control over your custom dialogs and can provide a wider variety of dialog effects. For example, you can easily create wizards, add control tips (similar to tool tips), respond to different events (change, click, mouse over, mouse down, and so on), and provide context-sensitive help.

Unlike DialogSheets, you also have full control over the loading and display of UserForms. There are four UserForm methods that control how a UserForm is handled by VBA. These are Load, Show, Hide, and Unload.

Load

Loads the UserForm in memory, but doesn't display it. This method is useful when your project has complex forms that take a long time to load. The Load method allows you to load these forms into memory at the beginning of the program or at other appropriate times so that they are available without delay when they are needed.

Show

Displays the UserForm. If the UserForm is not already loaded into memory, the Show method will also load it.

Hide

Removes the UserForm from the screen and sets its Visible property to False, but does not remove it from memory. When a UserForm is hidden, all of the properties and methods that it exposes are still available.

Unload

Removes the UserForm from memory. When a UserForm is unloaded, its properties and methods are no longer available.

Any UserForms not explicitly unloaded by your add-in can potentially remain in memory until Microsoft Excel is closed. To avoid this, it's a good idea to clean up any loaded UserForms before exiting your add-in. You accomplish this with the UserForms collection. The UserForms collection contains all the loaded UserForms in your add-in. Running the following code ensures that all UserForms are unloaded:

If UserForms.Count > 0 Then
    For iCount = 1 To UserForms.Count
        Unload UserForms(0)
    Next iCount
End If