Help with For loop

[Pookiemeister]

On my userform I have 2 listboxes and 25 Comboboxes each contain a list of the same people's name. What I need to happen is when a user selects a name from any of the listboxes or comboxes it deletes that value from all the comboboxes and listboxes. I bolded the text that doesn't work. ctrl always equals nothing and does not run the next line of code. Thank you.

Private Sub ListBox1_Change()
   On Error GoTo myExitSub

    Dim Res As Variant
    Dim i As Integer, l As Integer, numselections As Integer
    Dim PTO As Range
    Dim ctrl As Controls
        
'Loop through every item in the ListBox
For i = 0 To ListBox1.ListCount - 1

    'Check if the item was selected.
    If ListBox1.Selected(i) Then
    
        numselections = numselections + 1
     
'find position of 'ListBox1.Selected(i)' in listbox items
        Res = Application.Match(ListBox1.List(i), ListBox1.List, 0)
        
        For Each PTO In ActiveSheet.Range("B17:D17").Cells
            
            If Len(PTO) = 0 Then
                PTO.Select
                Debug.Print ActiveCell.Address
                If ActiveCell.Address > "$C$17" Then GoTo myExitSub
                ActiveCell.Value = ListBox1.List(i)
                Exit For
            End If
        
        Next
      
        If Not IsError(Res) Then
            [B]For Each ctrl In Me.Controls
                If TypeName(ctrl) = "Listbox" Or "Combobox" Then
                    ctrl.RemoveItem Res - 1 ' subtract 1 because the index of is zero-based.
                End If
            Next ctrl[/B]
        End If
        
    End If

Next i


For l = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(l) = True Then
        numselections = numselections + 1
    End If
Next l
MsgBox numselections



myExitSub:
    Exit Sub
End Sub

 

[rlv01]

Instead of this:

If TypeName(ctrl) = "Listbox" Or "Combobox" Then

Try this:

If TypeName(ctrl) = "Listbox" Or TypeName(ctrl) = "Combobox" Then

 

[Pookiemeister]

Ctrl still equals "Nothing"

 

[rlv01]

Ctrl still equals "Nothing"

Not when I try it.

            If Not IsError(Res) Then
                Dim CType As String
                For Each ctrl In Me.Controls
                    CType = TypeName(ctrl)
                    Select Case CType
                    Case "ListBox", "ComboBox"
                        ctrl.RemoveItem Res - 1       ' subtract 1 because the index of is zero-based.
                    End Select
                Next ctrl
            End If

 

[Pookiemeister]

Ctrl still equals "Nothing". What am I missing?

Private Sub ListBox1_Change()
   On Error GoTo myExitSub

    Dim Res As Variant
    Dim i As Integer, l As Integer, numselections As Integer
    Dim PTO As Range
    Dim ctrl As Controls
        
'Loop through every item in the ListBox
For i = 0 To ListBox1.ListCount - 1

    'Check if the item was selected.
    If ListBox1.Selected(i) Then
    
        numselections = numselections + 1
        'MsgBox numselections

        'find position of 'ListBox1.Selected(i)' in listbox items
        Res = Application.Match(ListBox1.List(i), ListBox1.List, 0)
        
        For Each PTO In ActiveSheet.Range("B17:D17").Cells
            
            If Len(PTO) = 0 Then
                PTO.Select
                Debug.Print ActiveCell.Address
                If ActiveCell.Address > "$C$17" Then GoTo myExitSub
                ActiveCell.Value = ListBox1.List(i)
                Exit For
            End If
        
        Next
      
        If Not IsError(Res) Then
                Dim CType As String
                For Each ctrl In Me.Controls
                    CType = TypeName(ctrl)
                    Select Case CType
                        Case "ListBox", "ComboBox"
                            ctrl.RemoveItem Res - 1       ' subtract 1 because the index of is zero-based.
                    End Select
                Next ctrl
        End If
    End If

Next i


For l = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(l) = True Then
        numselections = numselections + 1
    End If
Next l
MsgBox numselections

myExitSub:
    Exit Sub

 

[rlv01]

Ctrl still equals "Nothing". What am I missing?

Change

Dim ctrl As Controls

To this

Dim ctrl As Control

"Control" is an object, "Controls" is a collection.

 

[Pookiemeister]

So if I change this "Dim ctrl As Controls" to "Dim ctrl As Control" would I not also need to do something to this line of code?
"For Each ctrl In Me.Controls" Thank you.

 

[rlv01]

No, that line must remain the same.

 

[Alex Blakenburg]

"Control" is an object > Singular > Each (singular) ctrl
"Controls" is a collection > Plural > Me.Controls (all the controls of the Listbox)