Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Modeless Userforms

Most of the dialogs that we normally come into contact with are modal, which is to say that neither the application nor the user can do anything until the form is dismissed. When the Show statement is processed, by default the application window is disabled (so none of the menus are available), the form is displayed and the code stops. Snippets of code can run in response to control events, but it's not until the user closes the form that execution continues on the line after the Show statement.

When a userform is shown modeless, however, code execution continues immediately after the Userform_Initialize and Userform_Activate event procedures have finished, with the userform remaining displayed. If the code comes to an end while a modeless userform is displayed, the form remains open and both the userform and the application window can be used.

NOTE

Excel 97 does not support modeless userforms.

Splash Screens

The simplest use for a modeless userform is as an introductory splash screen. The userform is shown modeless at the start of the Auto_Open or Workbook_Open procedure and unloaded at the end of the procedure. Listing 10-23 shows a simple example, where the form uses the SetUserformAppearance procedure from earlier to remove the title bar.

Listing 10-23. Showing a Splash Screen at Startup

Sub Auto_Open()
Dim frmSplash As FSplashScreen
'Show the form modelessly
Set frmSplash = New FSplashScreen
frmSplash.Show vbModeless
'Process the startup code
Application.Wait Now + TimeValue("00:00:5")
'Unload the splash screen
Unload frmSplash
Set frmSplash = Nothing
End Sub
'The FSplashScreen Userform's Code Module
Option Explicit
'Set the form to have no title bar
Private Sub UserForm_Initialize()
'Adjust the height for the missing caption
Me.Height = Me.InsideHeight
SetUserformAppearance Me, uwsNoTitleBar
End Sub
'Prevent the form being closed using Alt+F4
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Cancel = (CloseMode = vbFormControlMenu)
End Sub

Progress Bars

A rather more interesting use of modeless forms is to display progress information to the user during lengthy looping operations. Figure 10-11 shows a simple progress bar userform, where the progress indicator is made up of two overlapping Frame controls, each containing a label. The back frame has a white background and a label with blue text, and the front frame has a blue background and a label with white text. As the progress is updated, the width of the front frame is adjusted, allowing us to see more of the blue background. This makes the bar appear to fill up as the progress increases.

Figure 10-11. A Modeless Progress Bar

The code for the progress bar form is too lengthy to show here, but is included on the CD in the ModelessForms.xls example workbook. The FProgressBar form can be copied from the example workbook into your project and controlled using code such as that shown in Listing 10-24.

Listing 10-24. Using the Progress Bar Userform

Sub ShowProgress()
Dim lLoop As Long
Dim lIterations As Long
Dim frmProgress As FProgressBar
lIterations = 2000
'Initialize the progress bar
Set frmProgress = New FProgressBar
frmProgress.Title = "Professional Excel Development"
frmProgress.Text = "Preparing reports, please wait..."
frmProgress.Min = 1
frmProgress.Max = lIterations
'Show the progress bar
frmProgress.ShowForm
For lLoop = 1 To lIterations
'Check if the user cancelled
If frmProgress.Cancelled Then Exit For
'Update the progress
frmProgress.Progress = lLoop
'Do Stuff
Next lLoop
'Unload the progress bar form
Unload frmProgress
End Sub

Combining with Menu Items

If we display a modeless userform and then allow our code to finish, the form is left active on the screen, and both the form and the application can be used. This behavior can be used to very good effect in form-based dictator applications. In this design, the worksheet is only ever used for a backdrop graphic display; all the interaction with the user is done through userforms. Most form-based applications have a central "switchboard" form, with a set of buttons to show subforms for each functional area. Those forms have their own buttons to show other forms and so on. It is usually very difficult to navigate around the application. If we use modeless userforms, however, the menus are available, so we can implement a menu structure that enables the user to quickly switch between parts of the application.

To implement this design, we need to be able to communicate with all the forms, so they can be notified when the user clicks a menu item to jump to another form, or when the application is about to exit, or if the Save menu item is clicked. All the forms will have to include the same set of standard functions, shown in Listing 10-25, that can be called from a central "form-handler" routine.

Listing 10-25. Standard Routines to Be Included in All Modeless Forms

' Called prior to navigating to another form.
' Allows the form to validate and store its data, then unload
' If validation fails, the navigation can be cancelled
Public Sub BeforeNavigate(ByRef Cancel As Boolean)
End Sub
' Called prior to saving the data workbook
' Allows the form to validate and store its data
' If validation fails, the navigation can be cancelled
Public Sub BeforeSave(ByRef Cancel As Boolean)
End Sub
' Called after to saving the data workbook
' Allows the form to update its display
' (e.g. if showing the file name)
Public Sub AfterSave()
End Sub
' Called when the application is about to be closed
' The form should unload itself, but could cancel the close
Public Sub AppExit(ByRef Cancel As Boolean)
End Sub

With all the userforms having the same set of standard routines, we can write a simple centralized routine to manage them all, shown in Listing 10-26.

Listing 10-26. The Central Control Routine to Handle Navigation Between Forms

' Global variable to hold the form currently being displayed
Dim gfrmActiveForm As Object
' A single OnAction procedure for most menu items, where the
' form name is obtained from the menu item's Parameter
Sub FormMenuClick()
ShowForm Application.CommandBars.ActionControl.Parameter
End Sub
'Common routine to switch between forms
Sub ShowForm(ByVal sForm As String)
Dim bCancel As Boolean
'If there's an active form, tell it to save and unload
If Not gfrmActiveForm Is Nothing Then
gfrmActiveForm.BeforeNavigate bCancel
End If
'If the save/close wasn't cancelled,
If Not bCancel Then
'Show the next form, assuming it is in the same workbook
Set gfrmActiveForm = VBA.Userforms.Add(sForm)
gfrmActiveForm.Show vbModeless
End If
End Sub
'The OnAction routine for the File > Save menu item
Sub MenuFileSave()
Dim bCancel As Boolean
'If there's an active form, tell it to save its data
If Not gfrmActiveForm Is Nothing Then
gfrmActiveForm.BeforeSave bCancel
End If
If Not bCancel Then
'Save the data workbook if not cancelled
gwkbDataWorkbook.Save
'If there's an active form, tell it to do its post-save
'processing (if any)
If Not gfrmActiveForm Is Nothing Then
gfrmActiveForm.AfterSave
End If
End If
End Sub

Using this mechanism, we can add more userforms to the application without having to add any extra code to control their display; as long as they include the standard set of procedures shown in