0

I have the following code in a UserForm. I'm attempting to add a certain subset of the comboboxes on the form to a collection, and then loop through that collection. It seems to be adding the value of the combobox to the collection rather than the control object itself...? What identifier or property do I need to use to be able to access the .Text property of the combobox object after it is added to the collection?

'Put comboboxes  in a collection
Dim oColl As Collection
Set oColl = New Collection
Dim cb As Control
For Each cb In Me.Controls
    If TypeName(cb) = "ComboBox" Then
        ' I suspect it may be this line going wrong:
        If IsNumeric(cb.Text) Then oColl.Add (cb)
    End If
Next

' Trying to loop through the collection of comboboxes
' I've tried to declare ctrl as MSForms.ComboBox, ComboBox, Control, Object,
' and Variant, results are the same...
Dim ctrl As MSForms.ComboBox
Dim i As Integer   
For i = 1 To oColl.count
    For Each ctrl In oColl
        ' This line produces an object required error on ctrl
        If CInt(ctrl.Text) = line Then
FredGooch
  • 191
  • 2
  • 13

1 Answers1

1

You're so close. It's just some syntax errors, i.e. collection.add with the brackets behind it.

Example: Loops through all comboboxes on userform, stores the ones that have a number in them in a collection. Loops through collection and displays message for each combobox.text. Fires upon CommandButton1 click event. Code goes in the userform itself, since we reference me.controls

  Private Sub CommandButton1_Click()
  Dim oColl As Collection
  Set oColl = New Collection

  Dim cb As Control

  For Each cb In Me.Controls
     If TypeName(cb) = "ComboBox" Then
         If IsNumeric(cb.Text) Then oColl.Add cb 'Indeed here you had unneeded brackets. Alternative would be "Call oColl.Add(cb)"
     End If
  Next cb

  For Each cb In oColl
     MsgBox cb.Text
  Next cb
  End Sub

Edit: You can just declare a variable ComboBox, but when you loop through all controls (For Each Control in Me.Controls) you will get a type mismatch error.

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
  • Thanks so much! Knew it was probably something simple. Why would the brackets cause it to add the value of the combobox rather than the object itself? – FredGooch Feb 04 '16 at 02:57
  • 1
    @JWarren that is extensively answered here... http://stackoverflow.com/questions/5413765/what-are-the-rules-governing-usage-of-brackets-in-vba-function-calls – Rik Sportel Feb 04 '16 at 07:35
  • Thanks, that cleared a lot up. I've had weird issues with this before and never bothered to look into the actual rules. – FredGooch Feb 04 '16 at 12:58