cell to textframe using characters object

  • Thread starter Thread starter Simon Prince
  • Start date Start date
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
 
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,
 
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
 
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
 
Back
Top