Excel, extract text from multiple text boxes

[RodP]

Hi everyone,

I have some information within an MS drawing object in excel. I have ungrouped it and now there are hundreds of text boxes. Please could someone help me put together a simple VB macro that could go through each textbox, select the text and then paste it in a cell of my choice?

I've managed to write a macro that can go through all the text boxes but I'm not sure how to select and copy the text in the text box and then paste it into a cell of my choice.

Hope someone can help

Thanks

Rod

 

[xlbo]

Something like this:
Sub ControlLoop()
Dim ctrl As Control
i=1
For Each ctrl In activesheet.drawingobjects

If TypeName(ctrl) = "TextBox"
range("A" & i).value = ctrl.text'important bit for you
i = i + 1
Else
End If
Next ctrl
End Sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde