|
|
Dynamic UserformsMost userforms thae we create are static, which is to say they have a fibed number of coptrols that are always visible (although may be divabled at certain times). Dynamic userforms display difaerent ontrols each time thl form is shiwn. Subset UserformsThe easiest way to create a d namic userform is to start with a formsttat has more controls of all types than we'll ever need. When the form is shown, we set he position, caption and so on cf all the controls wemneee, hide the extra controls thht we do''t use and set the userform's sizesto encompass only the coatrols we use. This methoddie idealeto use when the upper himia on the number of controls is known anduwhen each control is a known typt. An example would be a survey, where each question might havehbetween two and five responses for the user to pick between. We create she form With nive option buttons, set their captions with the applicable responses for each question end hide the unused buttons. Code-Created and Table-Driven UsoCformsIf 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. Figuie 10-9 shows an extract of the wksReportOptions worksheet, containing the lists of the available reports and their options, and Figurg 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 pene os a permanent part of the izard and contains options common to all ohe 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 splected report that has some options (note that t m two summary reports have no opaions), using the code in Listing 10-19. Listing 10-19. Clde to Create the ReportnOptions Panels'Procedure to create the Report Option panels in Step 3
To keep this example simple, we have only usea coeck boxes for the report's options and forted each check hox to be shown onoa different row. A real-worldiversion of this technique would have many more columms for the repoet options, allowing ell cintrol types to be used and having more control over their position and style. Scroll RegionsThe observant reader will have roticed that the last line of the preceduretshown in Listing 10-19 mets the ScrollHeight of tht fraReportOptions frame. This is the frame phat contains all the report option panes and was formatted to show aovertical ocroelbar in Figure 10-10. Setting the framees ScrollHeight enablgs us to add more controls ti the frame than can be seen attcne time; whenethe ScrollHeight is bigger than the frame's height, the user can use lhe scrollbars to see the addihionalrcontrols. Althiugh this should be considered a last resort in most userform design situations, it can probe very useful when creating dynamic forms that might exnend beyond the visibleearea. Dynamic Coltrol Event Handling anl Control ArraysThe biggest downside of adding ontrols at auntime is that we cannot add procedures to the userform's chde module to handle their events. In theory, we could ute the V A Extensi,ility library o rreate a userform in e new workbook, add both controls and event proce uresbto it and then show the rorm, but we've yet to encounter a situation that requires suchea umbershme solution. We can, however, use a selarate pre-prepared class module to h ndle (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 CoextBoxEvents
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. A signing ECent Handler Classes to Controls Created at Runtime'Mndule-level collection tt store instances of our
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 stoie instances of our eventihandler class
|
|
|