Change text in Textbox on Chart?

[Matt Williamson]

Is there some limit to the amount of text that can be changed in code for a
textbox embedded on a chart? I have the following code:

Sub FixChart()

With Chart1
.HasTitle = True
.ChartTitle.Text = Sheets("Index Figures").Range("A16")
.Shapes("Text Box 2").TextFrame.Characters.Text = Sheets("Index
Figures").Range("A17").Value
End With

Sheets("Index Figures").Range("A17").WrapText = True

End Sub

I put this in the sheetactivate, activate and open events of my workbook.

If I type test into Cell A17 of Sheet Index Figures and click over to my
Chart1 sheet, the textbox updates to test. If I Copy and Paste a large block
of text into cell A17 and click over to Chart1, it doesn't update. The code
is running though. I've tried clicking into cell A17 after the copy paste
and it still doesn't change the text box on the chart. It only changes if I
manually type a value. What event can I use to cause this thing to update?
If not an event, what code will cause it to update?

TIA

Matt

 

[TWR]

After you paste the text into the text bok, try manually adding a space at
the end of the text. I bet it works.

 

[Tushar Mehta]

You don't have to use VBA for this. Once you add the textbox to the chart,
select the textbox (make sure you haven't selected the text area), type the
equal sign (=), click the cell whose value you want in the textbox, and press
ENTER. Now, the textbox is linked to the cell. For more see
Data Labels
http://www.tushar-mehta.com/excel/newsgroups/data_labels/index.html

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Jon Peltier]

While Tushar's suggestion is how I would choose to approach this issue, I've
also dealt with text limits in textboxes. Through Excel 2003, the limit was
(about) 255 characters at a time, but you could keep adding more blocks. I
believe this limit is much greater in Excel 2007.

I wrote the following routine to enter the long text string strTxt into the
shape shpTxt. The procedure splits the long text into shorter segments, then
inserts them in reverse order into the shape.


Sub InsertTextIntoTextbox(shpTxt As Shape, strTxt As String)
Dim iLen As Long
Dim iCount As Long
Dim iIndex As Long
Dim sSplit() As String

Const dLen As Long = 250

iLen = Len(strTxt)
iCount = iLen \ dLen
ReDim sSplit(0 To iCount)

For iIndex = 0 To iCount
sSplit(iIndex) = Mid$(strTxt, 1 + iIndex * dLen, dLen)
Next

shpTxt.TextFrame.Characters.Text = sSplit(iCount)

For iIndex = iCount - 1 To 0 Step -1
With shpTxt.TextFrame.Characters(1, 1)
.Insert sSplit(iIndex) & .Text
End With
Next

End Sub


- Jon