Can you reference and manipulate Form field names as variables?

[catfunt]

Hi,

This is simplified version of what I am trying to do.

I have an Excel form called aForm with three textboxes:

textBox1
textBox2
textBox3

What I would like to do is check the contents of each from one function by making the textbox names variables, but I am stuck with code like this:

01 IF aForm.textBox1.value = "something" THEN
02 'do some stuff
03 ELSE
04 'do some different stuff
05 END IF
06
07 IF aForm.textBox2.value = "something" THEN
08 'do some stuff
09 ELSE
10 'do some different stuff
11 END IF
12
13 IF aForm.textBox3.value = "something" THEN
14 'do some stuff
15 ELSE
16 'do some different stuff
17 END IF


In my mind, I should be able to do something like this:

01 PUBLIC SUB Main()
02
03 DIM someMessage As String
04 DIM textboxName As String
05
06 FOR i = 1 TO 3
07
08 textboxName = "aForm.textBox" & i
09 someMessage = checkText(textBoxName)
10
11 NEXT i
12
13 END SUB
14
15 FUNCTION checkText(textBoxToCheck As ?)
16
17 IF textBoxToCheck = "something" THEN
18 'do something and return value
19 checkText = <some value>
20 ELSE
21 'do something and return value
22 checkText = <some value>
23 END IF
24
25 END FUNCTION


Simplified, is it possible to reference form fields as variables so that you can iterate through them in a loop? I have a feeling that there must be a way to do this.

Many thanks in advance,
Dave

 

[HughLerwill]

>I have an Excel form ...

So your question may be better handled in forum707

However handling the TextBoxes like this may help.

Private Sub CommandButton1_Click()

Dim ArrayOfTbs(1 To 3) As Variant

Set ArrayOfTbs(1) = TextBox1
Set ArrayOfTbs(2) = TextBox2
Set ArrayOfTbs(3) = TextBox3

For Each tb In ArrayOfTbs()
n = n + 1
tb.Text = n
Next

End Sub

 

[catfunt]

Hugh,

Many thanks for the useful info. I believe that you have solved my problem and the key is "SET". To apply it to what I need to do I need to do it without the arrays:

Dim nonArray As Variant
Set nonArray = Chalkboard.l1
MsgBox (nonArray.Value)

I think this is going to work as I can now pass the variant into a function.

Many thanks!
Best,
Dave

 

[Andrzejek]

If you just have 3 text boxes on your Form, you may skip the [blue]blue[/blue] part of the code, but if you have several text boxes, assign something (like "TB") to the Tag of the text boxes you want to check.

Option Explicit

Private Sub CommandButton1_Click()
Dim ctrl As Control

For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.TextBox Then
        [blue]If ctrl.Tag = "TB" Then[/blue]
            MsgBox CheckText(ctrl)
        [blue]End If[/blue]
    End If
Next ctrl

End Sub

Private Function CheckText(txtb As MSForms.TextBox) As String

If txtb.Text = "ABC" Then
    CheckText = "Hurray"
Else
    CheckText = "No Much"
End If

End Function

Have fun.

---- Andy