How to get an array to help me identify a text box

[ilya2004]

I have a script that goes through all of the controls in my form and if it matches some criteria it will add the name of the form to an array.

At the end I have an array with all of the names of the controls that I would like to do more things with.

I would like to know, how can I have VBA interact with the controls based on the names that have been put in the aray.

Here is an example of what I am trying to express. The code in the bracket is what I need to know how to express correctly.

I am trying clear the the contentes of the control whose name has been stored in MyArray(2):

frmMyForm.[MyArray(2)].Clear

Thanks,
Ilya

 

[ZVI]

Hi Ilya,
See the example of referencing to controls using it names:

' Code in Usefrorm
Dim a()

Private Sub CommandButton1_Click()
  
  Dim i As Long
  
  For i = 0 To UBound(a)
    
    ' Enum controls in ControlTipText
    Controls(a(i)).ControlTipText = "Ctrl#" & i
    
    ' Clear value of each texbox
    If TypeOf Controls(a(i)) Is MSForms.TextBox Then
      Controls(a(i)).Value = ""
    End If
    
    ' Print controls names
    Debug.Print i, Controls(a(i)).Name
    
  Next

End Sub

Private Sub UserForm_Initialize()
  Dim i As Long
  ReDim a(0 To Me.Controls.Count - 1)
  For i = 0 To UBound(a)
    a(i) = Controls(i).Name
  Next
End Sub

Regards

 

[ilya2004]

Thank you, this definitely would do what I had described. What I need, though, is something a bit more basic. The example of clearing was just something I used to write an expression to try to communicate my point. In reality the names of the controls are going to be used on a loop where thousands of cells are going to be compared to the values in the text boxes. The purpose of gathering all of the names into an array is so that only the relevant Controls are compared to the cells, instead of all of them. This would speed up the execution of the code tremendously.

If you have a better idea how to do do this, that would be good, but only piece that I am missing right now is how to refer to the control once I have the names stored in my array.

Thanks,
Ilya

 

[ZVI]

Let’s assume that the item of array a(1) = "TextBox1"
Then Me.Controls( a(1) ) is the reference to Me.TextBox1

If your code runs slowly, for fast comparing values of array1 with array2 (or with range values) I would copy values of array1 to the Dictionary object to compare array2 with that copy by the aid of the Dictionary’s Exists method. It’s much faster than looping array2 for each value of array1.

You can search in Board "Scripting.Dictionary" to find examples of its implementation.
Or post small part of your testing data and the code you stuck with.