Databases Reference
In-Depth Information
Sub ClearComboBoxes()
'This will clear every ComboBox in the active GUI
Dim c As Control
For Each c In Controls
If TypeOf c Is ComboBox Then
'Debug.Print c.Name
'The Clear Method is not Present in Autocomplete!
c.Clear
End If
Next c
End Sub
Notice that object variable
c
is dimensioned as type
Control
. What this means is that
c
will be
representative of any controls found on the active form or which are active in the Excel environment. A
For
loop is then set up to loop through all the controls. This
For
loop will loop through every control
that exists in the form. However, only the combo boxes are of interest as they are the only elements to
have action taken on them. So, within the loop body, the
TypeOf
statement is utilized to check and see
if the type of control is a combo box. If the current control in the loop is a combo box, then its contents
are cleared utilizing the
Clear
method. What is important to keep in mind here is that this methodology
could be utilized for any type of control or any type of object within Excel. Such routines can be utilized
to set or check any properties or methods of any control, group of controls, or objects. Notice that the
clear method is not supported by the autocomplete functionality, yet it does in fact exist. (Figure 3.7).
A logical person might think: Why should the routine loop through all the controls when the
only control of interest is the combo box? It is tempting to utilize a routine such as the following
to clear the combo boxes, but for some reason it does not seem to work. This may or may not be
a bug in the Excel VBA paradigm; however, no information the author has found explains why
such a routine should not work.
Sub ClearComboBoxes_NoWork()
'One would think this would loop through all comboboxes
'and clear them - It will not.
Dim cmbx As ComboBox
For Each cmbx In Controls
cmbx.Clear
Next cmbx
End Sub
FIGURE 3.7
Not all object methods are supported by Autocomplete.
