Text Box Question



I have several text boxes on a worksheet that I'd like to loop through
and collect the input.


collects the information in textbox1. The following loop successfully
collects the data from all 6 text boxes.

n = 0 ' array is option base 0
For x = 1 To 6
my_info = ActiveSheet.OLEObjects("TextBox" & x).Object
ReDim Preserve retire_array(n)
info_array(n) = yy
n = n + 1

My question is, is there a more concise construction then "my_info =
ActiveSheet.OLEObjects("TextBox" & x).Object" that I could use? I've
tried things like

my_info = activesheet.textbox(x)
my_info = activesheet.textboxes(x).text
my_info = activesheet.textboxes(x)
my_info = activesheet.textboxes(x).text

but none of these work. Is there some short construct other than the
one above using the Objects.object approach that will work?..TIA, Ron



Peter T

You can also reference ActiveX objects as a Shape object, then via
sh.DrawingObject, but to read the text property you will still have to go
via the Object property of the OLEObject.

In passing, if you know (for certain as it seems) why the Redim Preserve.
Here's a couple of other ideas

Sub Test()
Dim arrTB() As Object
Dim colTB As Collection

ReDim arrTB(1 To 3)
Set colTB = New Collection

With ActiveSheet.OLEObjects
For i = 1 To 3
Set arrTB(i) = .Item("Textbox" & i).Object
colTB.Add .Item("Textbox" & i).Object, "Textbox" & i
End With

MsgBox arrTB(1).Text
MsgBox colTB(2).Text
MsgBox colTB("Textbox3").Text

End Sub

Peter T

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question