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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"Matt Williamson" wrote in message
...
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