Accessing Mutiple TextBoxes

[TimBro]

Hi,

I`m simply trying to export the text out of Multuple TextBoxes on
differnt Excel Worksheets in an ACCESS DB. This works fine as long as
I address only one TextBox:

Dim Text as String ' Temporary text Storage
Dim TableName as String ' Name of Tabel I want to access

' Name of TextBox1 = Comment

Text = Worksheets(TableName).Comment.Text

I have a List of all of the TextBox Names and now wanted to loop
through then always writing the content into the temporary String
"Text" and then export the Data. How can I address/access the text in
different TextBoxes during runtime ? The Item funktion doesn`t work:
e.g

Dim TextBoxName as String ' name of the TextBox I want to access

Text = Worksheets(TableName).Item(TextBoxName).Text

Thanx in advance for your help
Tim

 

[Peter T]

Hi Tim,

Just for ideas, assumes Textboxes from the drawing toolbar:

Sub Test()
Dim tbArr() As String, i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
For Each ws In ActiveWorkbook.Worksheets
If getText(tbArr, ws) Then
Debug.Print ws.Name
For i = 1 To UBound(tbArr)
Debug.Print tbArr(i, 1), tbArr(i, 2) 'Ctrl G
Next
End If
Next
End Sub

Function getText(Arr, ws As Worksheet) As Boolean
Dim tb As TextBox, n As Long
n = ws.TextBoxes.Count
If n = 0 Then Exit Function
getText = True
ReDim Arr(1 To n, 2)
n = 0
For Each tb In ws.TextBoxes
n = n + 1

Arr(n, 1) = tb.Name
Arr(n, 2) = tb.Text
Next

End Function

Regards,
Peter