extract values from text boxes (1 Viewer)

[Bogzla]

extract values from text boxes (Solved)

EDIT: I figured out a way to do this, scroll down to the last post if interested...

so I have some text boxes on a worksheet, and I want to loop through and extract the data from each of them..
I could get it by

string = string & activesheet.txtBox1.Value
string = string & activesheet.txtBox2.value
etc
etc

but this is kind of messy. plus the number of text boxes is dynamic

so I looked around on the net and found some code like:

Dim shape As shape

For Each shape In ActiveSheet.Shapes
    If Left(shape.Name, 3) = "txt" Then
Debug.Print shape.Name
Debug.Print shape.TextFrame.Characters.Text
    End If
Next

now, as far as I can tell, this should work... the name gets printed ok, but when the "shape.TextFrame.Characters.Text" line is hit, I get an error message:
"run-time error '438':
object doesn't support this property or method"

which is kind of strange as I have found that particular coding on a couple of sites
(eg: http://exceltips.vitalnews.com/Pages/T1024_Placing_Textbox_Text_Into_a_Worksheet.html)
and VBE's autocomplete is happy with it.

can anyone point out what I'm doing wrong here as I'm close to tearing hair out... do I need to enable a particular reference or something like that?

thanks in advance,

Bogzla

 

[shades]

Howdy. A couple of points.

1. You should never Dim a word that also refers to a object name (i.e. "shape").

2. Notice that in the example on the link the thing that is dimmed is a string, not an object. You can't mix the two.
________
Wage Insurance Dicussion

 

[Bogzla]

good point...

I've modified the code to be, erm, better written...

Dim str As String
Dim shp As shape

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, 3) = "txt" Then
        str = shp.Name
Debug.Print str
        str = shp.TextFrame.Characters.Text
Debug.Print str
    End If
Next

but I still get the same error at the:
str = shp.TextFrame.Characters.Text
line. If i comment out as follows:

Dim str As String
Dim shp As shape

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, 3) = "txt" Then
        str = shp.Name
Debug.Print str
'        str = shp.TextFrame.Characters.Text
'Debug.Print str
    End If
Next

it quite happily prints me out a list of textboxes whose name begins 'txt', so I know the loop is principally correct, it just doesn't like "shp.TextFrame.Characters.Text" which is kind of strange as the auto-complete is happy with this (in fact i made sure to use the auto-complete to avoid typos...)

dammit, is there another way to do this?
I'm getting kind of frustrated with the whole thing tbh...

EDIT: to further clarify, what I'm trying to do is find a way to access the value in a textbox by calling it with a string so that I can create a loop to get values from many textboxes... I've experimented with the:

ActiveWorkbook.Sheets("Sheet1").txtBox2.Value

type code, but it seems that the 'txtBox2' portion must be hardcoded and cannot be substituted with a string. If I am wrong on this, or if there is another, easier method to do this someone shout at me!

thanks,
Bogzla

 

[Bogzla]

My solution

For anybody that is interested, I figured a way to access textbox values by using OLEobjects:

Dim str As String
Dim o As Object

For Each o In ActiveSheet.OLEObjects
    If Left(o.Name, 7) = "TextBox" Then
        str = o.Name
        Debug.Print str
        str = o.Object.Value
        Debug.Print str
    End If
Next

This code now happily prints me out the name and value for any object whose name starts with "TextBox"

Have a nice day