Copy textbox contents

S

Stuart

Cannot find how to do this, even via the macro recorder:

Copy the contents of a Drawing toolbox textbox to a
range in a new sheet, resulting in the range displaying the
contents exactly as did the textbox.

Is this possible, please?

Regards.
 
D

Dave Peterson

If there's lots of text in the textbox:

http://support.microsoft.com/default.aspx?id=148815
How to Copy Text to TextBoxes Using the Characters Method

If you don't need that KB article:

Option Explicit
Sub testme01()
Dim myTB As TextBox
With Worksheets("sheet1")
Set myTB = .TextBoxes("text box 1")
.Range("a1").Value = myTB.Text
End With
End Sub

(I'm not sure what exactly means. If you mean wrap in the same spot--caused by
automatic line breaks--not enter keys, then I think you'll have lots of
trouble.)

Good luck,
 
S

Stuart

Many thanks.

I've actually 3 textboxes:
TextBox 11 positioned over the range "A12:A62"
TextBox 10 positioned over the range "B12:D62"
TextBox 12 positioned over the range "E12:E62"
and I'm copying the contents to a sheet in a new book.

This seems to work, and to preserve the layout of the
text in the original textbox:

Option Explicit
Sub testme02()
Dim TB1 As TextBox, TB2 As TextBox, TB3 As TextBox

With Workbooks("Book2").Worksheets("Sheet1")
.Range("A12:A62").MergeCells = True
.Range("A12:A62").WrapText = True
.Range("A12:A62").VerticalAlignment = xlTop
.Range("B12:D62").MergeCells = True
.Range("B12:D62").WrapText = True
.Range("B12:D62").VerticalAlignment = xlTop
.Range("E12:E62").MergeCells = True
.Range("E12:E62").WrapText = True
.Range("E12:E62").VerticalAlignment = xlTop
End With

With Workbooks("xxx-Contract Master RFI.xls") _
.Worksheets("Contract RFI")
Set TB1 = .TextBoxes("text box 11")
Workbooks("Book2").Worksheets("Sheet1") _
.Range("a12").Value = TB1.Text
Set TB2 = .TextBoxes("text box 10")
Workbooks("Book2").Worksheets("Sheet1") _
.Range("b12").Value = TB2.Text
Set TB3 = .TextBoxes("text box 12")
Workbooks("Book2").Worksheets("Sheet1") _
.Range("e12").Value = TB3.Text
End With
End Sub

Regards.
 
Top