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

ilya2004

Board Regular
Joined
Mar 17, 2011
Messages
135
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):

Code:
frmMyForm.[MyArray(2)].Clear

Thanks,
Ilya
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,802
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi Ilya,
See the example of referencing to controls using it names:
Rich (BB code):

' 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

Board Regular
Joined
Mar 17, 2011
Messages
135
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,802
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top