none
Excel 2003 Chart Text Box No Longer "calculates" in 2010 RRS feed

  • Question

  • Hello,

    I searched around and could not find an answer.

    I have a chart that have several text boxes. These text boxes have forumlas associated with them that reference cell values. When these cell values are updated the text boxes on the chart do not update.

    This was behaviour that is working when workbook is opened in Excel 2003. Stopped working when opening the workbook in Excel 2010. Tried converting the workbook but that did not help. Any advice?

    Thanks

    Tuesday, July 19, 2011 5:50 PM

Answers

  • Hi Sam and Calvin

    I have the same problem.=> But after working on it for a while I found a "work-around"

    The automatic calculate doesn't fix the problem. (I always have Automatic "on" except when debugging VB code)

    I have a very complex workbook with many sheets developed in MacExcel2004 with lots of VB (which worked fine in 2004).

    I am now working in WinExcel2010 and, like Galvin, it seems any of the Text Boxes that came over from the "OLD" versions don't automatically update when i change numbers in the spreadsheet.

    All else works correctly including all formulas in other sheets, all VB functions and Macros and all figures in sheets.

    "NEW" text boxes Ive added in using Excel2010 also work!!!

    It seems only the "OLD" Text Boxes dont update.

    SOLUTION:

    I went in to one of the "OLD" text boxes and pushed enter after clicking in the box as if I had entered a new formula. The formula updated correctly and it continued to update correctly when I change numbers somewhere else in the spreadsheet. Essentially it makes the "OLD" text box operate like a "NEW" text box. It also retained it "NEW" update characteristics after saving and reopening the workbook.

    ==> So solution is to go click in each "OLD" text box and push ENTER. Kind of a pain but workable.

    MS: Clearly the calculate linkage update in Excel2010 is not quite up to snuff. I suspect there is a linkage mapping that is not updated from the old excel to the new excel.

    Cheers

    Jim Leuer


    Monday, January 2, 2012 5:17 AM

All replies

  • Works for me. What do the formulas in your text boxes look like? A simple =Sheet1!$A$1 in a text box works in Excel 2003 and 2010.
    Ed Ferrero
    www.edferrero.com
    Wednesday, July 20, 2011 12:36 AM
    Answerer
  • I have a chart that have several text boxes. These text boxes have forumlas associated with them that reference cell values. When these cell values are updated the text boxes on the chart do not update.

    Hi Sam,

     

    Thanks for your post.

     

    I think you need to change the Calculation Options to “Automatic”. I guess your current setting for this property is “Manual”.

    How to change Calculation Options:

    Formula tab -> last group “Calculation” -> Calculation Options -> “Automatic”

     

    Please let me if you can fix the issue via the above steps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 20, 2011 4:16 AM
    Moderator
  • Hi Sam,

    Any update on this problem? Have you solved the problem?


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 25, 2011 12:25 PM
    Moderator
  • Hi Sam and Calvin

    I have the same problem.=> But after working on it for a while I found a "work-around"

    The automatic calculate doesn't fix the problem. (I always have Automatic "on" except when debugging VB code)

    I have a very complex workbook with many sheets developed in MacExcel2004 with lots of VB (which worked fine in 2004).

    I am now working in WinExcel2010 and, like Galvin, it seems any of the Text Boxes that came over from the "OLD" versions don't automatically update when i change numbers in the spreadsheet.

    All else works correctly including all formulas in other sheets, all VB functions and Macros and all figures in sheets.

    "NEW" text boxes Ive added in using Excel2010 also work!!!

    It seems only the "OLD" Text Boxes dont update.

    SOLUTION:

    I went in to one of the "OLD" text boxes and pushed enter after clicking in the box as if I had entered a new formula. The formula updated correctly and it continued to update correctly when I change numbers somewhere else in the spreadsheet. Essentially it makes the "OLD" text box operate like a "NEW" text box. It also retained it "NEW" update characteristics after saving and reopening the workbook.

    ==> So solution is to go click in each "OLD" text box and push ENTER. Kind of a pain but workable.

    MS: Clearly the calculate linkage update in Excel2010 is not quite up to snuff. I suspect there is a linkage mapping that is not updated from the old excel to the new excel.

    Cheers

    Jim Leuer


    Monday, January 2, 2012 5:17 AM
  • I have a chart that have several text boxes. These text boxes have forumlas associated with them that reference cell values. When these cell values are updated the text boxes on the chart do not update.

    Hi Sam,

     

    Thanks for your post.

     

    I think you need to change the Calculation Options to “Automatic”. I guess your current setting for this property is “Manual”.

    How to change Calculation Options:

    Formula tab -> last group “Calculation” -> Calculation Options -> “Automatic”

     

    Please let me if you can fix the issue via the above steps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    This is clearly NOT the answer, would you unmark it please. It is not helpful to people who come here looking for help to see posts marked as answers when they are not.

    Also, can you please stop using your posts to advertise products? I'm pretty sure that Microsoft promised not to open these forums to advertising.


    Ed Ferrero
    www.edferrero.com
    Wednesday, January 11, 2012 6:04 AM
    Answerer
  • Well, I have pie-charts and a lot of text boxes referencing to cell values. I tried selecting data in eahc pie charts and clicked Enter. Similarly - as suggested above, selected each text box and pressed Enter on their formulae.

    But the charts and text boxes dont get updated when their corresponding data changes (I admit my calculation option is set to manual because I am dealing with more than 60K rows in each sheet, my VBA after generating the data applies Calculation at the end of processing).

    To update the charts... I do something as below (reset data references...). I think I may have to similar VBA on Text Box Shapes.

        Set sht = Workbooks(tmplBk).Sheets("Charts")
        For Each co In sht.ChartObjects
            co.Activate
            For Each sc In ActiveChart.SeriesCollection
                sc.Select
                temp = sc.Formula
                sc.Formula = "=SERIES(,,1,1)"
                sc.Formula = temp
            Next sc
        Next co

    Monday, September 23, 2013 4:51 PM