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

my_info=activesheet.textbox1

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
Next

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

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
Next
End With

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

End Sub

Regards,
Peter T


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

my_info=activesheet.textbox1

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
Next

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