Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Change text in Textbox on Chart?
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 |
#2
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Change text in Textbox on Chart?
After you paste the text into the text bok, try manually adding a space at
the end of the text. I bet it works. "Matt Williamson" wrote: 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 |
#3
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Change text in Textbox on Chart?
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/ne...els/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 "Matt Williamson" wrote: 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 |
#4
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Change text in Textbox on Chart?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for selecting a textbox within a chart? | Charts and Charting in Excel | |||
Formating textbox on a chart | Charts and Charting in Excel | |||
Why does my textbox disappear when I put it on a chart? | Charts and Charting in Excel | |||
How to change the return of a textbox from TRUE/FALSE to YES/NO? | Excel Discussion (Misc queries) | |||
manipulating textbox in chart | Charts and Charting in Excel |