Text box reference 1

[rwttm001]

I want to grab the text from numerous text boxes within excel and insert it into sequential cells.

My first issue is that I can't reference any of the text boxes as I don't know their name property - when I right click on the object there is no properties menu as with combo boxes etc.

Any help would be greatly appreciated.....

MWR

 

[SkipVought]

Hi,

Are these textboxes on a sheet or on a userform?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rwttm001]

On a sheet.....

 

[SkipVought]

    r = 1
    For Each Sht In ActiveSheet.Shapes
       With Sht.DrawingObject
          Cells(r, 1).Value = .Object.Text
       End With
       r = r + 1
    Next

this will work if...
1. only textboxes are on the sheet and
2. the order does not matter

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rwttm001]

Thanks for that but still no joy.

I'm using Excel 2000, and have searched through object browser for DrawingObject but no joy. Do you have different classes to me? Also what do I need to decare Sht as?

Finally there are lines and ovals on the sheet too but I should be able to error handle around that.

What I really need to do is to return the text from inside the text box then I think I'll be ok.....

Thanks a lot

MWR

 

[xlbo]

You don't need to dim Sht as anything really (although i suppose you should dim it as shape if you have option explicit)
Skip - little confusing as you are using sht as the object holder for shapes - I'd suggest using:
for each shp in activesheet.shapes

Very much doubt that you have different classes - works for me....

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[rwttm001]

There is some confusion here I think in the definition of text box!!

The sheet I've inherited has lots of text boxes drawn from the drawing toolbar, as opposed to from the control toolbox as with combo boxes etc.

The main issue with this is I can't reference the boxes to obtain the text - even the name of the boxes appears to be duplicated.

Sorry for the confusion...

MWR

 

[rwttm001]

Solved

Amended Skips code having stolen code recorded in a Macro....

Dim r As Integer
Dim mycapture As String

r = 1

On Error Resume Next

For Each Sht In ActiveSheet.Shapes
With Sht.Select

mycapture = Selection.Characters.Text
Range("A" & r & "&quot = mycapture

r = r + 1

End With
Next

thanks a lot...