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(msoTextOrientationHoriz ontal, 0,
100, 50, 50)

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

End Sub

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
(msoTextOrientationHorizontal, 0,
100, 50, 50)

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

End Sub
.

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

--
Regards,
Tom Ogilvy



"Peter Atherton" wrote in message
...
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
(msoTextOrientationHorizontal, 0,
100, 50, 50)

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

End Sub
.

Thanks for the responses. My question was really related to why I
couldn't assign a characters object that I'd read from a cell to the
textframe.characters (object?) of a textbox. Having read the help a
bit more, I believe that the answer is 'The Characters object isn't a
collection. For the TextFrame object, Characters is a method.'

I do, of course, take the point that you can transfer the text, font
name, font size etc one by one (and character by character). I was
trying to find a quicker way of doing this.

Simon

"Tom Ogilvy" wrote in message ...
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

--
Regards,
Tom Ogilvy



"Peter Atherton" wrote in message
...
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
(msoTextOrientationHorizontal, 0,
100, 50, 50)

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

End Sub
.