Use For Loop to obtain values from multiple textboxes in Excel VBA

Question

For a VBA code in Excel (not a userform) I have 4 textboxes named TextBox1 to TextBox4.

I want to obtain the values from these textboxes using a for loop. The goal is to search for a value in a corresponding column (D in this case). Therefore I search in the selected sheet (named after the selected value in the UpdateComboBox). To be able to search I first select the rownumber corresponding to the Weld showed in RelatedWeldTextBox_i. This value differs following other input boxes.

The code below is wrong. It creates a String with the for loop: TextBox1 for example. But it starts searching column D for the String TextBox1 instead of the value presented in this textbox. If I use Weld = Textbox1.Value The code works but only for Textbox1 of course...

Sub RelatedWeldNegative ()

Dim Weld As String
Dim Bmin As Integer
Dim Bmax As Integer 
Dim i As Integer
For i = 1 To 4`

Weld = "Textbox" & i & ".Value"
        For Each Cell In ActiveWorkbook.Sheets(UpdateComboBox.Value).Range("C2:C320")
           If Cell.Text = Weld Then
            SelecRow = Cell.Row
            End If
Next

Next i
Bmax = 6
Bmin = ActiveWorkbook.Sheets(UpdateComboBox.Value).Cell(SelecRow, "D")
'follow up with code
End Sub

Thanks in advance,

Miriam

What *kind* of textbox? There's more than one kind and they are accessed somewhat differently from VBA. Can you describe precisely how they were created? — RBarryYoung, Feb 24 '14 at 15:04
possible duplicate of [Uncheck all checkboxes across entire workbook via CommandButton](http://stackoverflow.com/questions/16818207/uncheck-all-checkboxes-across-entire-workbook-via-commandbutton). Please look [**HERE**](http://stackoverflow.com/questions/16818207/uncheck-all-checkboxes-across-entire-workbook-via-commandbutton/16818828#16818828) as it demonstrates how to iterate over Form and ActiveX controls — , Feb 24 '14 at 15:17

score 3 · Answer 1 · edited Feb 24 '14 at 15:45

3

Here is how to loop over TextBoxes:

Sub dural()
    For i = 1 To 4
        ActiveSheet.Shapes("TextBox " & i).Select
        MsgBox Selection.Characters.Text
    Next i
End Sub

Another Way

Sub Sample()
    Dim shp As Shape
    Dim i As Long

    For i = 1 To 4
        Set shp = ActiveSheet.Shapes("TextBox" & i)

        Debug.Print shp.OLEFormat.Object.Object.Text
    Next i
End Sub

edited Feb 24 '14 at 15:45

Siddharth Rout

147,039
17
206
250

answered Feb 24 '14 at 15:24

Gary's Student

95,722
10
59
99

1

+ 1 I think you have an Extra space in `"TextBox "` and also you don't need to select it :) – Siddharth Rout Feb 24 '14 at 15:46
I put the Extra space in because on my system **Insert** automatically names them with the extra space................it is nice to see a way to get the text without the **Select** ..........thanks! – Gary's Student Feb 24 '14 at 15:49
Ah ok. On my sheet it doesn't insert that space :) – Siddharth Rout Feb 24 '14 at 16:10

Use For Loop to obtain values from multiple textboxes in Excel VBA

1 Answers1