What Collection are ComboBoxes components of?

[Oak]

I have 10 ComboBoxes that need resetting in my program and wanted to run a For Each cbo in Collection Next cbo loop, but after trying a number of possibilities, I couldn't ascertain what Collection ComboBoxes were in? Would someone know?
Thank you

 

[Karl E. Peterson]

I have 10 ComboBoxes that need resetting in my program and wanted to run a
For Each cbo in Collection Next cbo loop, but after trying a number of
possibilities, I couldn't ascertain what Collection ComboBoxes were in? Would
someone know? Thank you

They're part of the Controls collection:

Private Sub UserForm_Click()
Dim cntl As Control
For Each cntl In Controls
If TypeName(cntl) = "ComboBox" Then
' Got one!
Debug.Print cntl.Name
End If
Next cntl
End Sub

Later... Karl

 

[Martin Seelhofer]

Hey there

Just a minor addition: In the following line, you could also
use dynamic type checking instead of string comparison:

[...]
If TypeName(cntl) = "ComboBox" Then
' Got one!
Debug.Print cntl.Name
End If
[...]

Here's what I mean:

[...]
If TypeOf ctrl Is ComboBox Then
' Got one!
...
End If
[...]

The advantage of this approach is that VBA does not allow
spelling errors. In the above example, you could always write
something like "CombboBox" which will not generate a
compiler error but will result in something else than the
expected runtime-behaviour. However, in the dynamic
type-checking example, VBA would complain when writing
something like ... TypeOf ctrl Is CombboBox ... since there
is no type CombboBox.


Cheers,
Martin

 

[Karl E. Peterson]

Better!
--
[Microsoft Basic: 1976-2001, RIP]

Hey there

Just a minor addition: In the following line, you could also
use dynamic type checking instead of string comparison:

[...]
If TypeName(cntl) = "ComboBox" Then
' Got one!
Debug.Print cntl.Name
End If
[...]

Here's what I mean:

[...]
If TypeOf ctrl Is ComboBox Then
' Got one!
...
End If
[...]

The advantage of this approach is that VBA does not allow
spelling errors. In the above example, you could always write
something like "CombboBox" which will not generate a
compiler error but will result in something else than the
expected runtime-behaviour. However, in the dynamic
type-checking example, VBA would complain when writing
something like ... TypeOf ctrl Is CombboBox ... since there
is no type CombboBox.


Cheers,
Martin