Loop Through VBA Variables

Question

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?

What you propose doesn't seem extreme to me. Does it not work when you try it? VBA UserForm objects only have 3 collections (pages, tabs, and controls), so your custom class must be in one of them. You could cycle through each printing the name of each item until you find what you seek. Alternatively, you could loop on the list and text boxes and run your custom search class that way instead of the other way around. — Word Nerd
– Word Nerd, Commented Dec 8, 2022 at 18:56
@WordNerd It does not seem to work when I run it. It's giving me a Run-time error (Could not find the specified object) on: With Me.Controls.Item("oEventHandler" & i) I also looped though all the controls on my userform and oEventHandler didn't come up. — Liam Russell
– Liam Russell, Commented Dec 8, 2022 at 21:04
If Karma's answer below doesn't work for your project, you might benefit from creating your own "collection" class for your custom clsSearchableDropdown class objects. Then you'll be able to iterate on them as members of that collection as you do now with form user controls. See this Answer and also this documentation from MS re VB that should hold true for VBA too. — Word Nerd
– Word Nerd, Commented Dec 9, 2022 at 17:52
@WordNerd Thank you! You steered me in the right direction. I posted the code that worked for me. — Liam Russell
– Liam Russell, Commented Dec 12, 2022 at 19:35

Liam Russell · Accepted Answer · 2022-12-12 19:39:50Z

1

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

edited Dec 12, 2022 at 19:39

answered Dec 12, 2022 at 19:32

Liam Russell

133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

karma · Accepted Answer · 2022-12-09 04:55:01Z

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.

Thank you for taking the time to write a comment and trying to help me! I was able to figure out what I wanted.

Collectives™ on Stack Overflow

Loop Through VBA Variables

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related