Solved: Multiple textboxes

[island17]

I have a VBA form with over 20 text boxes. I need to be able to print the contents of those textboxes after the user fills them out. I thought I could use a "for" loop since I need to add a vbcr after each. I am using the generic naming convention (textbox1,textbox2 ect.), and thought I could increment my way along. (no dice)
Any ideas?

Thanks
Island17

[Graham Skan]

You can step through the controls like this. Not sure which app & hence what the print might do.

[vba]Dim i as integer
For i = 1 to 20
Print UserForm1.Controls("textbox" & i).Text & vbcr
next i[/vba]

[Tommy]

The below code will show a message box with the contents for each textbox. They are in the order in which the textbox was inserted. This code runs in the form itself.

[VBA]
Private Sub PrintTextBoxes()
Dim PText As Control
For Each PText In UserForm1.Controls
'If This control is a textbox
If InStr(1, PText.Name, "TextBox") > 0 Then
MsgBox PText.Text ' Display the text
End If
Next
Set PText = Nothing 'clean up
End Sub
[/VBA]

Let me know if I can be of further assistance.

[Steiner]

I'd use a slightly different code for determining whether I want the control or, so it does not matter what the textbox is named like as long as it is a textbox:


[VBA]
Private Sub CommandButton1_Click()
Dim Ctrl As Control, Temp$

Temp = ""
For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is TextBox Then
Temp = Temp & Ctrl.Text & vbCrLf
End If
Next Ctrl

MsgBox Temp
End Sub
[/VBA]

[Anne Troy]

Any luck here, Island?

[Iamaterp]

Hi, I found your post on VBA Express and it was very helpful!!! I am using it, and would like to expand on it alittle further. The code you provided was for taking the text in multiple textboxes, and combining it into one textbox. I was able to successfully do that however, I am now trying to also copy in the label in front of the textbox as well.

Please see my example below, and let me know if you could be of any help. Thanks!

desired results in 1 textbox (both the label and textbox contents combined):
field1: my
field2: is
field3: to

my results:
field1: my
field1: is
field2: is
field2: to
field3: to


my code:
[VBA] Private Sub Command9_Click()
Dim PText As Control
Dim Temp As String
Dim label As String
Dim combine As String
Dim combine2 As String


For Each PText In Me.Controls
If InStr(1, PText.Name, "lbl") > 0 Then
label = PText.Caption
ElseIf InStr(1, PText.Name, "field") > 0 Then
PText.SetFocus
Temp = PText.Text
End If
combine = label & Temp & vbCrLf
combine2 = combine2 & combine
Next PText

Text6.SetFocus
Text6.Text = combine2

End Sub[/VBA]

[Tommy]

Hi Iamaterp,

The below code will fix the duplicates, if any questions just ask.

[VBA]
Private Sub Command9_Click()
Dim PText As Control
Dim Temp As String
Dim label As String
Dim combine As String
Dim combine2 As String


For Each PText In Me.Controls
If InStr(1, PText.Name, "lbl") > 0 Then
label = PText.Caption
ElseIf InStr(1, PText.Name, "field") > 0 Then
'remove this PText.SetFocus
'Temp = PText.Text
Temp = Temp & PText.Text
End If
'combine = label & Temp & vbCrLf
'combine2 = combine2 & combine
Next PText
combine = label & Temp & vbCrLf
combine2 = combine2 & combine
Text6.SetFocus
Text6.Text = combine2
End Sub
[/VBA]

HTH

[Iamaterp]

Thanks Tommy!!!!!!!!