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

Control Specifics

Most of the controls that we use in our forms are well documented and well understood, so documenting them here would be of little benefit to the reader. Instead, this section of the chapter explains how to use some of the lesser-known controls, or how to use them in innovative ways.

ComboBox

The ComboBox is the unsung hero of the MSForms toolbox. By changing the style of the drop-down button, we can use a combo box as a normal drop-down list, as a text box, as a filename entry box or as a totally customized drop-down control. Figure 10-12 shows four combo boxes, with the bottom one shown in its dropped state, revealing a custom drop-down pane for specifying a filter.

Figure 10-12. Combo Box Styles

Drop-Down List/Combo

The default behavior for a combo box is to allow the user to select an item from a list or type in entries that do not exist in the supplied list; when the arrow is clicked, the list is shown below the combo box. When the user clicks on an item from the list, the selected item is shown in the control and the list is hidden.

Text Box

If we set the ShowDropButtonWhen property to fmShowDrop-ButtonNever and the Style property to fmStyleDropDownCombo, the result is a control that looks and behaves exactly like a text box. This allows us to have a single control that can be used to select an item from a list, or allow direct entry. For example, in the userform in Figure 10-12, the top drop-down may be a list of attributes about a person, such as age, sex and so on, whereas the second drop-down would be used to fill in the value for the selected attribute. When Age is selected from the drop-down, we would want to be able to type a number directly into the control, but when Sex is selected, we would want to show a drop-down to choose between Male and Female.

Filename Box

By changing the DropButtonStyle to fmDropButtonStyleEllipsis, we create a control that looks like a filename box. The user would expect a File Open dialog to appear when he clicks the button. We can do exactly that by hooking the DropButtonClick event, as shown in Listing 10-27.

Listing 10-27. Handle the Ellipsis in the Filename Combo

'Handle clicking the ellipsis in the Filename combo
Private Sub cboFileName_DropButtonClick()
Dim vFile As Variant
'Get the filename
vFile = Application.GetOpenFilename()
'Write it to the control
If TypeName(vFile) = "String" Then
cboFileName.Text = vFile
End If
'Toggle the Enabled property to move the focus
'to the next control
cboFileName.Enabled = False
cboFileName.Enabled = True
End Sub

One annoying aspect of hooking the DropButtonClick event is that we can't cancel it, so the control shows an empty list after we've obtained the filename. One workaround for this is to toggle the Enabled property of the control, which forces the focus to move to the next control in the tab order.

Drop-Down Panes

The fourth combo box shown in Figure 10-12 is used to implement a totally customized drop-down pane, to display a simple filter selection. This would typically be used above a list box, to filter the items in the list. The code to handle the filter pane is shown in Listing 10-28.

Listing 10-28. Code to Manage a Custom Drop-Down Pane

'Boolean to identify if the filter has changed
Dim mbFilterChanged As Boolean
'Set up the form
Private Sub UserForm_Initialize()
cboFilter.AddItem "All Clients"
cboFilter.ListIndex = 0
cboConsultant.List = Array("Stephen Bullen", "Rob Bovey", _
"John Green")
cboConsultant.ListIndex = 0
End Sub
'When clicking the drop-down, show the filter frame
Private Sub cboFilter_DropButtonClick()
mbFilterChanged = False
fraFilter.Visible = True
fraFilter.SetFocus
End Sub
'Changing any of the filter options
'sets the 'Filter Changed' Boolean
Private Sub optAllClients_Click()
mbFilterChanged = True
cboConsultant.Enabled = optClientsForConsultant.Value
End Sub
Private Sub optClientsForConsultant_Click()
mbFilterChanged = True
cboConsultant.Enabled = optClientsForConsultant.Value
End Sub
Private Sub cboConsultant_Change()
mbFilterChanged = True
End Sub
'When exiting the frame, check for updates to the filter
Private Sub fraFilter_Exit(ByVal Cancel As _
MSForms.ReturnBoolean)
CheckFilterFrame
End Sub
'When clicking outside the frame,
'check and close the filter panel
Private Sub UserForm_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
CheckFilterFrame
End Sub
'Handle clicking outside the frame,
'to check for updates and close the panel
Private Sub CheckFilterFrame()
'If it's visible, update the list
If fraFilter.Visible Then
If mbFilterChanged Then ApplyFilter
End If
fraFilter.Visible = False
End Sub
'Apply the changed filter options
Private Sub ApplyFilter()
'Update the text of the filter dropdown
If optAllClients Then
cboFilter.List(0) = "All Clients"
Else
cboFilter.List(0) = "Clients for " & cboConsultant.Text
End If
'Update the contents of the list box
End Sub

The custom drop-down pane is a standard Frame control, initially set to be invisible and with a slightly lighter background color, containing the controls used for our filter. When the user clicks the combo box's drop-down button, we use the DropButtonClick event to initialize a Boolean variable mbFilterChanged (used to identify whether changes are made within the frame), then make the frame visible and give it the focus; this makes the frame appear to "drop down" from the combo box. We include code in the change event for all the controls in the frame to set the Boolean variable to True, indicating that the frame's content has changed. The user can exit the frame by tabbing to or clicking another control (causing the Exit event to fire), or by clicking somewhere else on the userform (which we detect with the Userform_MouseDown event). In both cases, we call the CheckFilterFrame procedure to hide the frame, check whether any of the controls were changed and apply the new filter. In this case, we're just updating the text shown in the combo box to show the filter settings. The combo box style is set to fmStyleDropDownList, so that clicking anywhere in the combo box will cause the DropButtonClick event to fire. We have a single item in the combo box's list, with the ListIndex to zero to show it in the control. To update the text shown in the combo box, we change the text of that item.

Windows Common Controls

There is an OCX file available on most computers called mscomctl.ocx, usually found in the C:\windows\system32 folder that contains a set of controls collectively known as the Microsoft Windows Common Controls 6.0. Although it theoretically might not exist, we have yet to see a computer with Office installed that doesn't have this fileit is so widely used that anything other than a plain vanilla Windows installation will include the file. It contains the following controls that can be used in our userforms. To access these controls, right-click the control toolbox, select the Additional Controls, menu and put a tick mark beside each of the controls you intend to use:

Microsoft ImageComboBox Control 6.0

Microsoft ImageList Control 6.0

Microsoft ListView Control 6.0

Microsoft ProgressBar Control 6.0

Microsoft Slider Control 6.0

Microsoft StatusBar Control 6.0

Microsoft TabStrip Control 6.0

Microsoft ToolBar Control 6.0

Microsoft TreeView Control 6.0

Microsoft UpDown Control 6.0

Some of these controls, such as the TabStrip and UpDown controls, are very similar to the standard MSForms controls, but the others can be used to improve the usability of our forms. For example, the ListView is similar to the File pane of Windows Explorer, and enables us to display a list of items with icons, giving us many formatting possibilities for each item in the list. The ListView's "report" style is very similar in appearance to the normal List control, but enables us to display each item using a different font, color and so forth. The TreeView control is an excellent way to display hierarchical data, and the ImageList and ImageCombo controls can be used where displaying thumbnails may be more appropriate than text.

To fully document each of the Windows Common Controls is beyond the scope of this book, but the CommonControls.xls example workbook contains the userform shown in Figure 10-13, with fully commented code to explain its operation.

Figure 10-13. Using the Windows Common Controls

[View full size image]

The official documentation for the Windows Common Controls can be found in the MSDN library within the Visual Basic 6.0 section. For example, the documentation for the TreeView starts at [ http://msdn.microsoft.com/library/en-us/cmctl198l/vbobjTreeView.asp ]. The only issue to be aware of when using the Windows Common Controls on userforms is that they do not like to be placed inside the MultiPage control. Trying to modify a control that is not on the active page usually fails.

Drag and Drop

The normal MSForms controls do not support drag-and-drop operations between controls. If we want to implement drag and drop between controls on our forms (such as being able to drag an item from one list box and drop it on another), we have to either use the Windows Common Controls or use a Visual Basic form. The CommonControls.xls workbook contains fully commented code that implements drag and drop between the ListView and TreeView controls as well as within the TreeView control to change its structure.