Referencing a textbox using a string in VBA

[Markb384]

Hi guys



To simplify my problem, imagine I have a bunch of textboxes on a form, all named textbox_A, textbox_B etc. To save having to type 26 lines of code, I decided to create a for loop instead, using the code beneath to set the value in each textbox to the corresponding letter. The current code is my latest attempt, but I have tried many variations including with and without delimiters, dots instead of bangs etc.

Dim loopLetter As String 
Dim loopInteger As Integer


    For loopInteger = 65 To 90
        loopLetter = "'textbox_" & Chr(loopInteger) & "'"
            [Forms]![Results]![loopLetter] = loopLetter
    Next loopInteger

How do I go about referencing the textbox using the variable from the loop?

Thank you

[ItsMe]

Hmmm.... here is an example I worked up to help you enumerate your controls in a form. You could incorporate the Select Statement to assign a value to a control once you found the one you need.


here is a list of control types. You could also assign a tag to controls of your choice if you only want to loop a specific group of controls on your form. Not sure how you want to choose the controls on your form.
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Here is some sample code. Use the immediate window in the VBA editor to view the results.

Dim ctl As Control
Dim intCount As Integer
intCount = 0
For Each ctl In Me.Controls
intCount = intCount + 1
Debug.Print "Item number " & intCount & " is a " & ctl.ControlType & vbCrLf
Select Case ctl.ControlType
Case acTextBox
Debug.Print ctl.Name & " is a textbox!"
End Select
Next

[Markb384]

Hi ItsMe

I solved it using

[Forms]![results].Controls(loopLetter).Value = = loopLetter

[ItsMe]

Nice use...