Looping of textbox values to an Array

[rjudge]

I won't admit to how long I have researched this and how many attempts I have tried to get a loop through textboxes to work. Clearly I am an idiot even for a newbie.

ActiveDocument.txtQuestion1.Value = value in the first textbox.

Yes, I know an array is silly since ideally I would just loop through the textboxes without having to store the values in an array since they are already in a collection. However, if you help me with the code below, I believe even I can do the rest.

This is what I have in code that solves my problem, but is clearly silly:


Dim stdAnsArray(numQuestions) As Variant
stdAnsArray(0) = ActiveDocument.txtQuestion1.Value
stdAnsArray(1) = ActiveDocument.txtQuestion2.Value
stdAnsArray(2) = ActiveDocument.txtQuestion3.Value
stdAnsArray(3) = ActiveDocument.txtQuestion4.Value
stdAnsArray(4) = ActiveDocument.txtQuestion5.Value
stdAnsArray(5) = ActiveDocument.txtQuestion6.Value
stdAnsArray(6) = ActiveDocument.txtQuestion7.Value


As mentioned I have tried various other coding such as:


For k = 0 to numQuestions-1
stdAnsArray(k) = "txtQuestion" & k+1.Value
Next

But this does not work. I have tried adding ActiveDocument in front of "txtQuestion" and that did not work.

Fortunately I am already bald.

Any help greatly appreciated.

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim arrValues() As String
Dim oILS As InlineShape, oTB As TextBox, lngCount As Long
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = 5 Then
Select Case oILS.OLEFormat.ClassType
Case "Forms.TextBox.1"
Set oTB = oILS.OLEFormat.Object
Select Case True
Case oTB.Name Like "txtQuestion" & lngCount + 1
ReDim Preserve arrValues(lngCount)
arrValues(lngCount) = oTB.Text
lngCount = lngCount + 1
End Select
End Select
End If
Next oILS
For lngCount = 0 To UBound(arrValues)
Debug.Print arrValues(lngCount)
Next lngCount
End Sub

[rjudge]

Thanks so much Greg - it did the trick. Not as simple as I thought it might be (at least from my perspective). I need to research why you had to create objects for the InlineShape, Textbox, and lngCount. I know it is fundamental, just haven't found the source to explain it properly to me yet. Plenty to keep me busy.

[fumei]

I would be interested in Greg's explanation.

[gmaxey]

I suppose there is no need to create the oTB as TextBox object variable.
I created the oILS variable because ActiveX controls are InlineShapes. So I looped through each inlineshape, checked to see if the shape was a ActiveX Textbox form control, and then checked to see if that form control was named with your txtQuestion convention. Otherwise all textbox controls would be added to the array.

lngType is a not an object but a long variable. It serves to dimension the array and provides the numerical part of the textbox name.

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim arrValues() As String
Dim oILS As InlineShape, lngCount As Long
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = 5 Then
Select Case oILS.OLEFormat.ClassType
Case "Forms.TextBox.1"
Select Case True
Case oILS.OLEFormat.Object.Name Like "txtQuestion" & lngCount + 1
ReDim Preserve arrValues(lngCount)
arrValues(lngCount) = oILS.OLEFormat.Object.Text
lngCount = lngCount + 1
End Select
End Select
End If
Next oILS
For lngCount = 0 To UBound(arrValues)
Debug.Print arrValues(lngCount)
Next lngCount
End Sub

Of course if the document contains only the 6 inlineshaped that are the textboxes txtQuestion1 - 6, and you are not concerned about more or fewer textboxes or other controls then the code can certainly be simplified:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim arrValues(5) As String, lngCount As Long
For lngCount = 0 To UBound(arrValues)
arrValues(lngCount) = ActiveDocument.InlineShapes(lngCount + 1).OLEFormat.Object.Text
Next lngCount
For lngCount = 0 To UBound(arrValues)
Debug.Print arrValues(lngCount)
Next lngCount
End Sub