Loop Through VBA Variables-CodePudding

I'm making a Userform in VBA where I have Text Box's where I can search items. I have about 30 Text Box's so I want to cut down the code using a loop instead of copy and pasting the same code 30 times.

Problem: I don't know how to loop through a public variable

Public Variable: Public oEventHandler(Number) As New clsSearchableDropdown
     oEventHandler would go from 1 to 30 (e.g oEventHandler2,oEventHandler3...oEventHandler30)
     clsSearchableDropdown is the Class Module for the search feature

Text Box: TextBox(Number)
ListBox: ListBox(Number)

Here is the original code (No Issue Just to Compare):

  With oEventHandler1
                            
    ' Attach the textbox and listbox to the class
                                        
        Set .SearchListBox = Me.ListBox1
        Set .SearchTextBox = Me.TextBox1
                                        
    ' Default settings
                                    
        .MaxRows = 10
        .ShowAllMatches = True
        .CompareMethod = vbTextCompare
        .WindowsVersion = False
                            
  End With

This is what I'm trying to do:

  Dim i As Integer

  for i = 1 to 30

    With Me.Controls.Item("oEventHandler" & i)
                            
      ' Attach the textbox and listbox to the class
                                        
          Set .SearchListBox = Me.Controls.Item("ListBox" & i)
          Set .SearchTextBox = Me.Controls.Item("TextBox" & i)
                                        
      ' Default settings
                                    
          .MaxRows = 10
          .ShowAllMatches = True
          .CompareMethod = vbTextCompare
        .WindowsVersion = False
                              
    End With

  Next i

I know that oEventHandler is not a control but is there a similar code I can use to loop through a public variable?

CodePudding user response：

If I understand you correctly, in the userform you have 30 Textboxes and 30 Listboxes, where each Textbox(N) is to search the value in the Listbox(N) located under that TextBox(N). So it looks something like this :

On the left side is TextBox01, under TextBox01 is ListBox01
On the right side is TextBox02, under TextBox02 is ListBox02

If the animation is similar with your expectation....

Preparation :

Make a named range (as many as needed) with something like List01, List02, List03, and so on for the value to populate each ListBox.
Name each ListBox with something like ListBox01, ListBox02, and so on.
Name each TextBox with something like TextBox01, TextBox02, and so on.

In the Userform module:

Dim MyTextBoxes As Collection

Private Sub UserForm_Initialize()

'populate the ListBoxes with value in a named range
Dim LBname As String: Dim RGname As String: Dim i As Integer
For i = 1 To 2
LBname = "ListBox" & Format(i, "00")
RGname = "List" & Format(i, "00")
Controls(LBname).List = Application.Transpose(Range(RGname))
Next i

'add each TextBox to class
Set MyTextBoxes = New Collection
    For Each ctl In Me.Controls
        Set TextBoxClass = New Class1
        If TypeName(ctl) = "TextBox" And InStr(ctl.Name, "TextBox") Then Set TextBoxClass.obj = ctl
        MyTextBoxes.Add TextBoxClass
    Next
    
End Sub

In the Class Module named Class1:

Private WithEvents tb As MSForms.TextBox

Property Set obj(t As MSForms.TextBox)
Set tb = t
End Property

Private Sub tb_Change()
Dim idx As String: Dim LBname As String: Dim arr
idx = Right(tb.Name, 2)
LBname = "ListBox" & idx
arr = Application.Transpose(Range("List" & idx))
    With Userform1.Controls(LBname)
        If tb.text = "" Then
            .Clear
            .List = arr
        Else
            .Clear
            For i = LBound(arr, 1) To UBound(arr, 1)
                If LCase(arr(i)) Like "*" & LCase(tb.value) & "*" Then .AddItem arr(i)
            Next i
        End If
    End With
End Sub

If in your userform you have another textbox which not to use as a search of the items in respective listbox, then maybe don't name the textbox with "TextBox" but something else, for example "blablabla".

if your existing textbox and listbox already named something like ListBox1, ListBox2, ListBox3 and so on, TextBox1, TextBox2, TextBox3 and so on... then name the named range like List1, List2, List3 and so on. In the class module, change the code for idx using the replace method, something like idx = replace(tb.name,"TextBox",""). Also in the Userform module for LBname and RGname use the replace method.

Because I'm limited in English language, I'm sorry I can't detail the code for further explanation.

CodePudding user response：

Here is the code that worked for me:

' Make a New Collection

  Dim coll As New Collection

' Add all Public Variables to Collection (n = Number 1 to 30)

  coll.Add uQuote.oEventHandler(n) 
     (e.g oEventHandler1, oEventHandler2... oEventHandler30)

  Dim i As Integer

  for i = 1 to 30

    With coll(i)
                            
      ' Attach the textbox and listbox to the class
                                        
          Set .SearchListBox = Me.Controls.Item("ListBox" & i)
          Set .SearchTextBox = Me.Controls.Item("TextBox" & i)
                                        
      ' Default settings
                                    
          .MaxRows = 10
          .ShowAllMatches = True
          .CompareMethod = vbTextCompare
        .WindowsVersion = False
                              
    End With

  Next i