Setting an array of text boxes equal to individual form text boxes

[lcaretto]

I am trying to develop a data input form that can test input variables for
validity. There are several inputs and I would like to use an array of text
boxes so that the error checking code can be done in a loop. (The maximum
and minimum values for each text box entry would also be arrays.)

When I try to set an array of text boxes (defined as a global array) to the
names of the individual text boxes (defined in the property windows of the
user form) I get a type mismatch error. By debugging the code I see that the
names of the individual text boxes on the right side of the equal sign are
using their default property so they appear to be strings instead of objects.

Here is an abbreviated version of the code I have used to test this concept:

Option Explicit
Const numberOfInputs = 3
Dim textBoxArray(1 To numberOfInputs) As TextBox
Sub userForm_initialize()
'Here is where the type mismatch error occurs.
Set textBoxArray(1) = txtA 'Text box names becomes strings at run time
Set textBoxArray(2) = txtB
Set textBoxArray(3) = txtC
End Sub
'Goal is to have error checking in a loop over all text boxes
Private Sub cmdSave_Click()
Dim varNo As Integer
For varNo = 1 To numberOfInputs
Call checkInput(varNo)
Next varNo
End Sub

Is there any way that I can set array components to refer to the scalar text
box objects (txtA, txtB, txtC).

Thanks for your help.

 

[Nigel]

You could use the Controls collection if on a UserForm or OLEObjects
collection if controls are on the worksheet, the value can then be passed to
your checkInput routine.

For i = 1 To 4
Controls("TextBox" & i).Value
Next i

For i = 1 To 4
ActiveSheet.OLEObjects("TextBox" & i).Value
Next i

 

[Peter T]

First of all this won't work

Dim textBoxArray(1 To numberOfInputs) As TextBox

Either use ReDim or hardcode numberOfInputs

Sub userForm_initialize()
'Here is where the type mismatch error occurs.
Set textBoxArray(1) = txtA 'Text box names becomes strings at run
time

If(?) txtA is a string do it like this
Set textBoxArray(1) = Me.Controls(txtA)

Is there any way that I can set array components to refer to the scalar
text
box objects (txtA, txtB, txtC).

No idea what you mean by "scalar tex box objects"

Regards,
Peter T

 

[Dave Peterson]

Excel has its own textbox--the one on the Drawing toolbar. If you don't specify
which textbox you want, then excel thinks you want to use its textbox.

Option Explicit
Const numberOfInputs = 3
Dim TextBoxArray(1 To numberOfInputs) As msforms.TextBox
Private Sub UserForm_Initialize()
Set TextBoxArray(1) = txtA
Set TextBoxArray(2) = txtB
Set TextBoxArray(3) = txtC
End Sub
Private Sub cmdSave_Click()
Dim varNo As Long
For varNo = LBound(TextBoxArray) To UBound(TextBoxArray)
Call CheckInput(varNo)
Next varNo
End Sub
Private Sub CheckInput(myIndex As Long)
MsgBox CBool(TextBoxArray(myIndex).Value <> "")
End Sub

MSForms.textbox is the ActiveX textbox (located on the control toolbar toolbox
and the userform controls toolbox).

The rest of your code worked fine for me.

I did change "as Integer" to "as Long" and varied the varno based on lbound()
and ubound() -- it's easier than hardcoding those limits when things change.

 

[lcaretto]

Nigel - thanks for your answer. First of all, I should have noted that I am
using a user form so I only needed your first solution.

I adopted your answer to my approach of getting an array of references using
the statement textBoxArray(index) = Controls(txtIndexName), where
txtIndexName is the name given to the text box in the properties window.

Your answer made me question my approach. Perhaps I should just use an
array of values, as implied in your answer, instead of an array of
references.

 

[lcaretto]

Peter -- thanks for the reply. I had already received on reply with the
suggestion to use the controls collection, which solved my problem.

In my code I used the statement "const numberOfInputs = 3" to set the array
dimension as (1 to numberOfInputs). This works fine and allows me to use the
const value (as numberOfInputs) in other parts of the code.

It is the same as hard coding the array size, but when the array size
changes I only have to change the value in the const statement to get it
right in all parts of the code.

 

[Peter T]

Peter -- thanks for the reply.

In my code I used the statement "const numberOfInputs = 3" to set the
array
dimension as (1 to numberOfInputs). This works fine and allows me to use
the
const value (as numberOfInputs) in other parts of the code.

Ah, that makes more sense.

Regards,
Peter T