Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Dynamic Userforms

Most userforms that we create are static, which is to say they have a fixed number of controls that are always visible (although may be disabled at certain times). Dynamic userforms display different controls each time the form is shown.

Subset Userforms

The easiest way to create a dynamic userform is to start with a form that has more controls of all types than we'll ever need. When the form is shown, we set the position, caption and so on of all the controls we need, hide the extra controls that we don't use and set the userform's size to encompass only the controls we use. This method is ideal to use when the upper limit on the number of controls is known and when each control is a known type. An example would be a survey, where each question might have between two and five responses for the user to pick between. We create the form with five option buttons, set their captions with the applicable responses for each question and hide the unused buttons.

Code-Created and Table-Driven Userforms

If we cannot predict a reasonable upper limit on the number of controls, or if there could be lots of different types of control that could be shown, having a pre-prepared set of controls on the form becomes increasingly harder to maintain. Instead, we can add controls to the userform at runtime. It's quite rare to find a situation that requires a userform to be created through code; we can usually either design the form directly or use the "subset" technique to hide the controls we don't need to use.

The one situation where code-created userforms make our development life extremely easy is in the use of table-driven dynamic wizards. Imagine a wizard used to generate a batch of reports, with each report using check boxes to set its options. In Step 2 of the wizard, we could display a multiselect list box of the available reports, where the list of reports is read from a table in a worksheet. When the user clicks the Next > button, we populate Step 3 of the wizard with the check boxes appropriate for the selected report(s), where again the check boxes are read from a worksheet table. By implementing a table-driven report wizard, we can add new reports to the wizard just by adding rows to the report and report options lists. An example of this technique can be found on the CD in the ReportWizard.xls workbook and is explained below.

Figure 10-9 shows an extract of the wksReportOptions worksheet, containing the lists of the available reports and their options, and Figure 10-10 shows Step 3 of the Report Wizard dialog, where the Client Detail report has been selected to run.

Figure 10-9. A List of Reports and Their Options

Figure 10-10. The Table-Driven Step 3 of the Report Wizard

In this step, the General Options pane is a permanent part of the wizard and contains options common to all the reports. The report-specific panes, such as the Client Detail pane, are created each time this step is initialized. A separate pane is created for each selected report that has some options (note that the two summary reports have no options), using the code in Listing 10-19.

Listing 10-19. Code to Create the Report Options Panels

'Procedure to create the Report Option panels in Step 3
Private Sub CreateReportOptions()
Dim vaOptions As Variant
Dim lReport As Long
Dim lOption As Long
Dim sReport As String
Dim fraFrame As MSForms.Frame
Dim chkControl As MSForms.CheckBox
Dim ctlControl As MSForms.Control
Dim dFraTop As Double
Dim dCtlTop As Double
'Constants for each column in the Report Options table
Const clREPORT = 1
Const clPARAM = 2
Const clCAPTION = 3
Const clDEFAULT = 4
'Read the report options table into an array
vaOptions = wksReportLists.Range("rngReportOptions").Value
'Clear out existing group boxes
For Each ctlControl In fraReportOptions.Controls
If TypeOf ctlControl Is MSForms.Frame And _
ctlControl.Name <> "fraGeneral" Then
fraReportOptions.Controls.Remove ctlControl.Name
End If
Next
'Get the position of the top of the first frame
dFraTop = fraGeneral.Top + fraGeneral.Height + 6
'Loop through the reports
For lReport = 0 To lstReports.ListCount - 1
'Was this one selected to run?
If lstReports.Selected(lReport) Then
'Get its name from the list box
sReport = lstReports.List(lReport)
'A new report, so clear the frame
Set fraFrame = Nothing
'Loop through the options array
For lOption = 1 To UBound(vaOptions)
'Is the option for the selected report?
If vaOptions(lOption, clREPORT) = sReport Then
'If we don't have a frame for this report,
'create one
If fraFrame Is Nothing Then
'Add a new frame to the dialog
Set fraFrame = fraReportOptions.Controls.Add( _
"Forms.Frame.1", "fraRpt" & lReport, True)
'Set the frame's size and position
With fraFrame
.Caption = sReport
.SpecialEffect = fmSpecialEffectSunken
.Top = dFraTop
.Left = fraGeneral.Left
.Width = fraGeneral.Width
End With
'Where to put the first control in the frame
dCtlTop = chkBlackWhite.Top
End If
'Add a check box to the report's frame
Set chkControl = fraFrame.Controls.Add( _
"Forms.CheckBox.1", _
vaOptions(lOption, clPARAM), True)
'Set its size and position, caption and value
With chkControl
.Top = dCtlTop
.Left = chkBlackWhite.Left
.Width = chkBlackWhite.Width
.Height = chkBlackWhite.Height
.Caption = vaOptions(lOption, clCAPTION)
.Value = GetSetting(gsREG_APP, gsREG_SECTION, _
vaOptions(lOption, clPARAM), _
vaOptions(lOption, clDEFAULT)) = "Y"
End With
'Move to the next control position
dCtlTop = dCtlTop + chkAutoPrint.Top - _
chkBlackWhite.Top
End If
Next
If Not fraFrame Is Nothing Then
'If we have a frame for this report, work out how
'high it needs to be
fraFrame.Height = fraGeneral.Height - _
chkAutoPrint.Top + dCtlTop - _
(chkAutoPrint.Top - chkBlackWhite.Top)
'Calculate the position for the next report's frame
dFraTop = fraFrame.Top + fraFrame.Height + 6
End If
End If
Next
'Set the scroll area of the Report Options frame,
'in case our report options don't fit
fraReportOptions.ScrollHeight = dFraTop
End Sub

To keep this example simple, we have only used check boxes for the report's options and forced each check box to be shown on a different row. A real-world version of this technique would have many more columns for the report options, allowing all control types to be used and having more control over their position and style.

Scroll Regions

The observant reader will have noticed that the last line of the procedure shown in Listing 10-19 sets the ScrollHeight of the fraReportOptions frame. This is the frame that contains all the report option panes and was formatted to show a vertical scrollbar in Figure 10-10. Setting the frame's ScrollHeight enables us to add more controls to the frame than can be seen at one time; when the ScrollHeight is bigger than the frame's height, the user can use the scrollbars to see the additional controls. Although this should be considered a last resort in most userform design situations, it can prove very useful when creating dynamic forms that might extend beyond the visible area.

Dynamic Control Event Handling and Control Arrays

The biggest downside of adding controls at runtime is that we cannot add procedures to the userform's code module to handle their events. In theory, we could use the VBA Extensibility library to create a userform in a new workbook, add both controls and event procedures to it and then show the form, but we've yet to encounter a situation that requires such a cumbersome solution. We can, however, use a separate pre-prepared class module to handle (most of) the events of the controls we add to the form. The class module shown in Listing 10-20 uses a variable declared WithEvents to handle the events of any TextBox it's hooked up to. The Change event is used to perform nonintrusive validation of the control, checking that it is a number using the CheckNumeric function from earlier. Ideally, we would prefer to use either the BeforeUpdate or AfterUpdate events to perform the validation, so it's done when the user leaves the control instead of every time it's changed. Unfortunately, those events belong to the generic MSForms.Control object and are not exposed to us when we declare a WithEvents object in this way.

Listing 10-20. Class to Handle a Text Box's Events

'Class CTextBoxEvents
'WithEvents variable to hook the events for a text box
Private WithEvents mtxtBox As MSForms.TextBox
'Allow the calling code to set the control to hook
Public Property Set Control(txtNew As MSForms.TextBox)
Set mtxtBox = txtNew
End Property
'Validate the text box with each change.
'Ideally, we'd using the AfterUpdate event, but
'we don't get it through the WithEvents variable
Private Sub mtxtBox_Change()
CheckNumeric mtxtBox
End Sub

Every time we add a text box to the form, we create a new instance of the class to handle its events and store all the class instances in a module-level collection, as shown in Listing 10-21.

Listing 10-21. Assigning Event Handler Classes to Controls Created at Runtime

'Module-level collection to store instances of our
'event handler class
Dim mcolEvents As Collection
'Build the userform in the initialize routine
Private Sub UserForm_Initialize()
Dim sBoxes As String
Dim lBoxes As Long
Dim lBox As Long
Dim lblLabel As MSForms.Label
Dim txtBox As MSForms.TextBox
Dim clsEvents As CTextBoxEvents
'Ask the user how many boxes to show
sBoxes = InputBox("How many boxes (1-5)?", , "3")
'Validate the entry
If sBoxes = " Then Exit Sub
If Not IsNumeric(sBoxes) Then Exit Sub
lBoxes = CLng(sBoxes)
If lBoxes < 1 Then lBoxes = 1
If lBoxes > 5 Then lBoxes = 5
'Initialize the collection of event handler classes
Set mcolEvents = New Collection
'Create the required number of boxes
For lBox = 1 To lBoxes
'Add a label to the form
Set lblLabel = Me.Controls.Add("Forms.Label.1", _
"lbl" & lBox)
With lblLabel
.Top = (lBox - 1) * 21.75 + 9
.Left = 6
.Width = 50
.Height = 9.75
.WordWrap = False
.Caption = "Text Box " & lBox
End With
'Add the text box to the form
Set txtBox = Me.Controls.Add("Forms.TextBox.1", _
"txt" & lBox)
With txtBox
.Top = (lBox - 1) * 21.75 + 6
.Left = 56
.Width = 50
.Height = 15.75
End With
'Create a new instance of the event handler class
Set clsEvents = New CTextBoxEvents
'Tell it to handle the events for the text box
Set clsEvents.Control = txtBox
'Add the event handler instance to our collection,
'so it stays alive during the life of the form
mcolEvents.Add clsEvents
Next
End Sub

We can use the same technique to handle the events of controls in nondynamic userforms as well. Imagine a form with 50 text boxes, all requiring numeric validation. We could include all 50 Change event procedures in our code and accept the maintenance overhead that brings, or we could use the class module from Listing 10-20 to handle the validation for all our text boxes. The code in Listing 10-22 iterates through all the controls on the form, hooking up new instances of the event handler class for every text box it finds.

Listing 10-22. Class to Handle a Text Box's Events

'Collection to store instances of our event handler class
Dim mcolEvents As Collection
'Hook the events for all the Text Boxes
Private Sub UserForm_Initialize()
Dim ctlControl As MSForms.Control
Dim clsEvents As CTextBoxEvents
'Initialize the collection of event handler classes
Set mcolEvents = New Collection
'Loop through all the controls
For Each ctlControl In Me.Controls
'Check if it's a text box
If TypeOf ctlControl Is MSForms.TextBox Then
'Create a new instance of the event handler class
Set clsEvents = New CTextBoxEvents
'Tell it to handle the events for the text box
Set clsEvents.Control = ctlControl
'Add the event handler instance to our collection,
'so it stays alive during the life of the form
mcolEvents.Add clsEvents
End If
Next
End Sub