Custom Dialog for Quick Stats

The following example of a custom dialog is built from a UserForm object and several ActiveX controls. The form window allows a user to quickly select a worksheet range and calculate some basic statistics. Furthermore, the form allows the user to summarize their work by writing the statistics for each selected range to a List Box control for later review. Figures 6.12 and 6.13 show the two pages of the MultiPage control used in the form's design.

Page I selected

MultiPage control -RefEdit control -Label controls

Page 1 of the summary statistics form design.

Page I selected

MultiPage control -RefEdit control -Label controls

- Combo Box control

- List Box control

• Command Button controls

- Combo Box control

- List Box control

• Command Button controls

Page 1 of the summary statistics form design.

The form contains a Combo Box, a List Box, and a RefEdit control that allow a user to select a workbook, worksheet, and cell range from the Excel application. Basic statistics are calculated from the selected range when the Command Button control with Caption property "Calculate" is clicked. The Command Button control with Caption property "Add to Summary" adds the stats to the List Box control on the second page of the MultiPage control. Two additional Command Button controls at the bottom of the form (Caption properties "Clear" and "OK") close the custom dialog and clear the List Box control on page 2 of the MultiPage control.

Table 6.5 summarizes the properties of the ActiveX controls that were changed from their default values at Design Time. Label controls that only serve to provide a textual label for other controls, and are not referenced in the program, are not listed in Table 6.5. Table 6.5 does not list any of the appearance properties that were changed in these controls. You should be able to recognize different fonts, colors, borders, and you will probably want to change them anyway to suit your personal preference.

Table 6.5 Property Settings of ActiveX Controls for the Summary Stats Custom Dialog r

Control

Label Label Label

Command Button Command Button Command Button

RefEdit Combo Box Combo Box Stats Page List Box Summary Page List Box Summary Page List Box Summary Page List Box

MultiPage MultiPage MultiPage

Page 1 on MultiPage control Page 1 on MultiPage control Page 2 on MultiPage control Page 2 on MultiPage control

Property

Name

TextAlign

BorderStyle

Name

Caption

Enabled

Name

Name

Style

Name

Name

ColumnCount ColumnWidths

Name

TabOrientation

Style

Name

Caption

Name

Caption

Setting l blCount, lblSum, lblMin, lblMax and so on fmTextAlignCenter fmBorderStyleSingle cmdCalcStats, cmdSummary, cmdClear, cmdOk

"Calculate", "Add to Summary", "Clear", "Ok"

False for cmdClear, True for other three Command Buttons refStats cmbWorkbooks fmStyleDropDownList lstWorksheets lstSummary

84 pt; 115 pt; 36 pt; 36 pt; 36 pt; 36 pt; 30 pt; 30 pt; 36 pt mpgSummary fmTabOrientationTop fmTabStyleTabs pgStats

"Stats"

pgSummary

"Summary"

The Style property of the Combo Box control can take one of two constant values; fmStyle-DropDownCombo, and fmStyleDropDownList. If the Style property is fmStyleDropDownCombo, the user can enter a value in the Combo Box control as well as choose from the list. If the Style property is fmStyleDropDownList, the control is essentially a List Box and the user must choose only from the list provided.

You can also change the number of data columns in the Combo Box and List Box controls from their default value of one. The ColumnCount and ColumnWidths properties set the number of data columns, and their widths (in points), respectively. Be sure to separate the different widths in the ColumnWidths property with a semicolon (;).

Microsoft uses the point for the size properties of the ActiveX controls in VBA. A point is 1/72 of an inch. You are probably more familiar with the point as a size unit for fonts. It's the same unit that describes the Width, Height, and ColumnWidths properties (and many others) of ActiveX controls.

The code for the Summary Stats dialog is contained entirely within its form module. All program code is entered into several event procedures of the ActiveX controls on the form. These procedures follow:

Option Explicit

Private Sub UserForm_Initialize() Dim wb As Workbook

For Each wb In Workbooks cmbWorkbooks.Addltem wb.Name If ActiveWorkbook.Name = wb.Name Then cmbWorkbooks.Value = wb.Name End If

Next mpgSummary.Value = 0 End Sub

The Initialize() event of the UserForm object serves to add the names of all open workbooks to the Combo Box control named cmbWorkbooks. A For/Each loop iterates through all the Workbook objects in the Workbooks collection and the AddItem() of the Combo Box control adds the name of each workbook to the list. When the active workbook is found, an If/Then decision structure ensures that the name of the active workbook is displayed in the edit area of the Combo Box control by setting the Value property of the control.

The last statement in the Initialized event procedure uses the Value property of the MultiPage control to ensure that the first page (index 0) of the MultiPage control is selected when the form is shown. If this statement is omitted, then the form is shown with the page that is selected while in design view of the VBA IDE.

Private Sub mpgSummary_Change()

If mpgSummary.SelectedItem.Caption = "Summary" Then cmdClear.Enabled = True

Else cmdClear.Enabled = False End If End Sub

The Change() event of the MultiPage control is triggered whenever the user selects a different page on the control. I use this event to enable or disable the Command Button control named cmdClear. This is the Command Button that clears the List Box on the second page of the MultiPage control. Since this Command Button only applies to the second page of the MultiPage control, it is disabled when the first page of the MultiPage control is selected. The Caption property of the Page object that is returned by the Selectedltem property of the MultiPage control tells the program what page is currently selected.

Private Sub cmbWorkbooks_Change() Dim ws As Worksheet

Workbooks(cmbWorkbooks.Value).Activate lstWorksheets.Clear

For Each ws In Worksheets lstWorksheets.Addltem ws.Name If ActiveSheet.Name = ws.Name Then lstWorksheets.Value = ws.Name End If

Next End Sub

The Change() event of the Combo Box control is triggered when the value of the control is changed. This trigger occurs when the user selects a new workbook from the list, and when the Initialized event of the UserForm object sets the Value property of the control; therefore, the code that adds the names of the worksheets in the active workbook to the List Box control is best placed in this event procedure.

First, a For/Each loop iterates through all Worksheet objects in the Worksheets collection and the AddItem() method of the List Box control adds the name of each worksheet to the list. Because I did not specify a Workbook object in the opening statement of the For/Each loop only the names of the worksheets from the active workbook are added to the List Box. An If/Then decision structure nested in the For/Each loop tests for equality between the name of the active worksheet and the Name property of the Worksheet object currently identified in the loop. When the condition is true, the Value property of the List Box control sets this name to be the selected item in the list.

Private Sub lstWorksheets_AfterUpdate()

Worksheets(lstWorksheets.Value).Select End Sub

The AfterUpdate() event is triggered after data in a control is changed through the user interface; therefore, when the user selects a new worksheet in the List Box control, the Value property of the List Box control is changed and the AfterUpdate() event is triggered. The single line of code in the AfterUpdate() event simply passes the new value of the List Box control to the Worksheets property of the Application object in order to select the new worksheet.

Private Sub refStats_DropButtonClick()

refStats.Text = "" End Sub

Private Sub refStats_Enter()

refStats.Text = "" End Sub

Earlier, I suggested that you avoid using the event procedures of the RefEdit control. For the most part, that recommendation remains; however, I have used the DropButtonClick() and Enter() event procedures in this program to clear text from the RefEdit control. These two event procedures trigger when the user selects the RefEdit control (either the drop button or edit area of the control). It's important that the text is removed from the RefEdit control before the user selects another worksheet range; otherwise, the new selection may be inserted into, rather than replace, the previous selection. After testing the program, these two events behaved—at least with these very simple program statements.

Private Sub cmdCalcStats_Click() Const NUMFORMAT = "#.00"

On Error Resume Next lblCount.Caption = Application.WorksheetFunction.Count _

(Range(refStats.Text))

lblSum.Caption = Application.WorksheetFunction.Sum _

(Range(refStats.Text)) lblMin.Caption = Application.WorksheetFunction.Min _

(Range(refStats.Text)) lblMax.Caption = Application.WorksheetFunction.Max _

(Range(refStats.Text)) lblMedian.Caption = Application.WorksheetFunction.Median _

(Range(refStats.Text)) lblAvg.Caption = Format(Application.WorksheetFunction.Average _

(Range(refStats.Text)), NUMFORMAT) lblStanDev.Caption = Format(Application.WorksheetFunction.StDevP _

(Range(refStats.Text)), NUMFORMAT)

End Sub

In the Click() event procedure of the Command Button control named cmdCalculate, Excel worksheet functions calculate the statistics that are written to the Label controls. The worksheet functions are passed Range objects created from the text entered in the RefEdit control. Note the use of line continuation characters with the excessively long statements and the Format() function to format the numerical output for the average and standard deviation such that only two decimal places are shown.

You probably noticed the statement On Error Resume Next in the CI i ck() event of the Command Button control cmdCalcStats. Adding this statement to a procedure prevents the program from crashing when it generates a Run Time error by sending program execution to the next line of code. I will discuss debugging and error handling in the next chapter.

Private Sub cmdSummary_Click() Dim curRow As Integer curRow = lstSummary.ListCount lstSummary.AddItem cmbWorkbooks.Value lstSummary.List(curRow, 1) = refStats.Text lstSummary.List(curRow, 2) = lblCount.Caption lstSummary.List(curRow, 3) = lblSum.Caption lstSummary.List(curRow, 4) = lblMin.Caption lstSummary.List(curRow, 5) = lblMax.Caption lstSummary.List(curRow, 6) = lblMedian.Caption lstSummary.List(curRow, 7) = lblAvg.Caption lstSummary.List(curRow, 8) = lblStanDev.Caption End Sub

The AddItem() method of the List Box and Combo Box controls only adds values to the first column of the control. When the ColumnCount property is greater than one, the List property must be used to add data to the other columns in the control. You can think of the List property as a two-dimensional array with the first index represented by the control's rows and the second index represented by the control's columns; thus, the code in the Click() event procedure of the Command Button control named cmdSummary makes perfect sense as it uses a row and column index with the List property to write the workbook name, selected range, and statistical values to the List Box. The ListCount property of the List Box control returns the number of items listed in the control and serves as the row index for setting the value of the List property. Index values for the List property start at zero, so the final column index representing the ninth column in the control is 8.

Private Sub cmdClear_Click()

lstSummary.Clear End Sub

Private Sub cmdOk_Click() Unload frmSamples End End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Unload frmSamples End End Sub

The last three procedures listed for the program are short and simple. The Click() event procedure of the Command Button control named cmdClear invokes the Clear() method of the List Box control to remove all of its listed items. The Click() event of the Command Button control named cmdOk and the QueryClose() event of the UserForm object are both used to close the form. They unload the form from system memory and end the program.

Figures 6.14 and 6.15 show both pages of the Summary Stats dialog form after running the program with some test data.

The Stats page on the Summary Statistics dialog.

The Stats page on the Summary Statistics dialog.

The Summary page on the Summary Statistics dialog.

The Summary page on the Summary Statistics dialog.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment