Need VBA Code to Select text boxes in spreadsheet

[Jaded in Cali]

I have a spreadsheet (Excel 2003) of data copied from a web page that has
critical data contained in a column of 400 text boxes. I need to remove the
text boxes from the spreadsheet, but retain the data. So far, I have been
able to address the text boxes one at a time from VBA and extract the data,
but I need to know how to address them from within the code. The text boxes
are named HTMLText1 through HTMLText400. I can generate the names using a
For loop, but using them to address the text boxes stumps me.

Dim strThisBoxName As String
Dim intValue As Integer
Dim g As Integer

For g = 1 To 1
strThisBoxName = "HTMLText" & g
intValue = [strThisBoxName].Value
Cells(g, "L").Value = intValue
Next g

returns an Invalid Qualifier error on the intValue = ... line.

Dim thisbox As textbox
Dim strThisBoxName As String
Dim intValue As Integer
Dim g As Integer

For g = 1 To 1
strThisBoxName = "HTMLText" & g
Set thisbox = Me.[strThisBoxName]
intValue = thisbox.Value
Cells(g, "L").Value = intValue
Next g

returns a "Needs Object" error on the Set thisbox... line.

I hope from the code, someone can see what I am trying to do and supply the
correct syntax to accomplish it.

Thank you in advance.

 

[B Lynn B]

I imagine your textboxes may be activex controls, in which case this should
work:

For g = 1 To 400
Cells(g, "L").Value = _
ActiveSheet.OLEObjects("HTMLText" & g).Object.Text
Next g

If somehow they were Shapes on your sheet, this should do it:

Dim g As Long
Dim myShp As Shape
Dim CharCount As Long

For g = 1 To 1
Set myShp = ActiveSheet.Shapes("HTMLText" & n)
CharCount = shp.TextFrame.Characters.Count
Cells(g, "L").Value = shp.TextFrame.Characters(1, CharCount).Text
Next g

Set myShp = Nothing

 

[Jaded in Cali]

Neither of those strategies worked. The OLE version returned an error: OLE
Object not found. The ActiveX version returned a similar error.

Clicking on Properties for the boxes, they are listed as HTMLText objects.
Visual Basic allowed me to Dim the ThisBox variable as "HTMLText" and
provides a HTMLText.value property, but I could not figure out how to address
the boxes using the loop variable.

Any clues would be greatly appreciated.

Thanks to all.