cell to textframe using characters object

S

Simon Prince

Hi,
Can anyone suggest why the following code doesn't work. I'm trying to
copy a worksheet cell's text and formatting to a shapes textbox
without having to loop through each character in turn and individually
set the formatting properties.

Any comments?

Sub textTochart()

Dim oursheet As Worksheet
Dim charstr As Characters
Dim tbox As Shape

Set oursheet = ActiveWorkbook.Worksheets("Sheet1")
Set tbox = oursheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0,
100, 50, 50)

Set charstr = oursheet.Cells(3, 2).Characters
Set tbox.TextFrame.Characters = charstr

End Sub
 
P

Peter Atherton

Simon

I've edited your code. It may not be the most effecient
but it works.

Sub textTochart()

Dim oursheet As Worksheet
Dim charstr As Variant
Dim tbox As Shape
Set oursheet = ActiveWorkbook.Worksheets("Sheet3")
Set tbox = oursheet.Shapes.AddTextbox
(msoTextOrientationHorizontal, 0, _
100, 50, 50)
charstr = oursheet.Cells(3, 2).Value
tbox.Select
Selection.Characters.Text = charstr

End Sub

Note Cell values always have to be Variant

Regards
Peter
-----Original Message-----
Hi,
Can anyone suggest why the following code doesn't work. I'm trying to
copy a worksheet cell's text and formatting to a shapes textbox
without having to loop through each character in turn and individually
set the formatting properties.

Any comments?

Sub textTochart()

Dim oursheet As Worksheet
Dim charstr As Characters
Dim tbox As Shape

Set oursheet = ActiveWorkbook.Worksheets("Sheet1")
Set tbox = oursheet.Shapes.AddTextbox
Click to expand...
(msoTextOrientationHorizontal, 0,
 
T

Tom Ogilvy

Note Cell values always have to be Variant

that is incorrect. If you know the cell contains a string, you can
certainly assign its value to a string variable. If you don't know what is
in the cell, you are always safe with variant.

The following works fine.

Sub textTochart()

Dim oursheet As Worksheet
Dim charstr As String
Dim tbox As Shape
Set oursheet = ActiveWorkbook.Worksheets("Sheet1")
Set tbox = oursheet.Shapes.AddTextbox _
(msoTextOrientationHorizontal, 0, _
100, 50, 50)
charstr = oursheet.Cells(3, 2).Value
tbox.Select
Selection.Characters.Text = charstr

End Sub
 
Top