none
Excel 2007 - Off-screen Line Chart missing RRS feed

  • Question

  • Hi,

    I use Excel 2007 to create a spread sheet containing several line charts. After re-open it, Excel only displays the charts which were on the screen when I closed it. I move up or down the spread sheet, the charts which were off the screen disappear. 

    If I stop the screen at some disappearing charts, then close and re-open it. The missing charts come back again. However, if I scroll up or down, the other charts disappear.

    If I disable an add-in, all charts can be displayed. However I cannot see any error in the add-in which would cause this issue. And it does not happen in every spread sheet. It occurs quite randomly.

    Could anybody help please?

    Thanks!

    Thursday, February 17, 2011 3:11 PM

Answers

  • Hi,

    I got two possible solutions here which can display all charts. But I am new to Excel programming, I do not know whether these two solutions do the same thing and whether they would cause other issues. Please advise.

     Public Sub refreshCharts()
      Dim c As Integer
      Dim n As Integer
      Dim aSheet As Object
      Dim temp As String
      Dim aChart As Excel.ChartObject


    solution 1:

            For Each aSheet In GetActiveWorkbook().Sheets
                If aSheet.Type = Excel.XlSheetType.xlWorksheet Then
                    For c = 1 To aSheet.ChartObjects.count
                        aChart = aSheet.ChartObjects(c)
                        aChart.Chart.Refresh()  
                    Next c
                End If
            Next aSheet

    solution 2:

            theApplication.ScreenUpdating = False
            For Each aSheet In GetActiveWorkbook().Sheets
                If aSheet.Type = Excel.XlSheetType.xlWorksheet Then
                    For c = 1 To aSheet.ChartObjects.count
                        aChart = aSheet.ChartObjects(c)
                        aChart.Activate()
                        For n = 1 To aChart.Chart.SeriesCollection.count
                            temp = aChart.Chart.SeriesCollection(n).formula
                            aChart.Chart.SeriesCollection(n).formula = temp
                        Next n
                    Next c
                End If
            Next aSheet
            theApplication.ScreenUpdating = True

    End Sub

    Many Thanks!

    • Marked as answer by Bessie Zhao Friday, February 25, 2011 9:24 AM
    Tuesday, February 22, 2011 3:46 PM

All replies

  • Hello Chasxu,

    Thanks for posting. To help me get a better understanding on this issue, would you please clarify some points here?

    1. What do you mean creating a spread sheet? Is it just a sheet like sheet1 or sheet2? Right?
    2. If it is just a common sheet like sheet1, you have inserted several line charts by going to Insert->Charts->Line. Right? Do you mean this issue happens when rolling the mouse on this spread sheet? If yes, please try to insert other types of chart and see if this issue also occurs.
    3. Would you please tell more information about this add-in? What the add-in is for? 

    I think we could try to troubleshoot this issue by doing more test. First, it is the one as I said above. Second, please pay attention to the number of the line chart, and see if it is also related. Third, we could check if there is any code about charts in that add-in. Also, if you have other machine with Excel 2007 installed on hand, please test it again. Hope this post could help you.

    If you have any concern on this post, just feel free to follow up. Have a nice day.


    Bessie Zhao [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.

    Friday, February 18, 2011 3:01 AM
  • Thanks for the reply. I will come back later.
    Friday, February 18, 2011 5:47 PM
  • Hi,

    I find the problem is located in the following function.

     Public Sub refreshCharts()
      Dim c As Integer
      Dim n As Integer
      Dim aSheet As Object
      Dim temp As String
      Dim aChart As Excel.ChartObject
    
      For Each aSheet In GetActiveWorkbook().Sheets
       If aSheet.Type = Excel.XlSheetType.xlWorksheet Then
        For c = 1 To aSheet.ChartObjects.count
         aChart = aSheet.ChartObjects(c)
         For n = 1 To aChart.Chart.SeriesCollection.count
          temp = aChart.Chart.SeriesCollection(n).formula 'Issue
          aChart.Chart.SeriesCollection(n).formula = temp 'Issue
          On Error GoTo 0
         Next n
        Next c
       End If
      Next aSheet
     End Sub
    

     If I comment out those two lines (marked with "Issue"), it works i.e. all the line charts can be displayed. Otherwise, the off-screen charts would disappear. Does anybody know why these two line would cause the issue? Thanks a lot!

    Monday, February 21, 2011 5:46 PM
  • Hello again Chasxu,

    Thanks for your code. I simply put the code into VBA editor. However, I am not able to reproduce this issue on my side by reopening the excel file. The excel file I use is here. Actually, I am not sure if the steps I used are right for reproducing this issue.

    Please check it and see if there is a difference between us. Is it because that I just use this code as VBA code not code in an add-in.

    Have a nice day.


    Bessie Zhao [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.

    Tuesday, February 22, 2011 6:28 AM
  • Hi Bessie,

    It does not always happen. Please see the sample which should have 7 charts.

    If you open it, (do not scroll up or down), then run the macro, the off-screen charts would probably disappear.

     

    And if I use "aChart.Chart.Refresh" not "temp=...formula; ...formula = temp", is there any difference?

     

    Thanks,

     

     

    Tuesday, February 22, 2011 10:20 AM
  • Hi,

    I got two possible solutions here which can display all charts. But I am new to Excel programming, I do not know whether these two solutions do the same thing and whether they would cause other issues. Please advise.

     Public Sub refreshCharts()
      Dim c As Integer
      Dim n As Integer
      Dim aSheet As Object
      Dim temp As String
      Dim aChart As Excel.ChartObject


    solution 1:

            For Each aSheet In GetActiveWorkbook().Sheets
                If aSheet.Type = Excel.XlSheetType.xlWorksheet Then
                    For c = 1 To aSheet.ChartObjects.count
                        aChart = aSheet.ChartObjects(c)
                        aChart.Chart.Refresh()  
                    Next c
                End If
            Next aSheet

    solution 2:

            theApplication.ScreenUpdating = False
            For Each aSheet In GetActiveWorkbook().Sheets
                If aSheet.Type = Excel.XlSheetType.xlWorksheet Then
                    For c = 1 To aSheet.ChartObjects.count
                        aChart = aSheet.ChartObjects(c)
                        aChart.Activate()
                        For n = 1 To aChart.Chart.SeriesCollection.count
                            temp = aChart.Chart.SeriesCollection(n).formula
                            aChart.Chart.SeriesCollection(n).formula = temp
                        Next n
                    Next c
                End If
            Next aSheet
            theApplication.ScreenUpdating = True

    End Sub

    Many Thanks!

    • Marked as answer by Bessie Zhao Friday, February 25, 2011 9:24 AM
    Tuesday, February 22, 2011 3:46 PM
  • Just wondering what those two lines are doing. Depending on the complexity of the series formula, after this line executes

    temp = aChart.Chart.SeriesCollection(n).formula

    temp may not look exactly like the formula bar. The single quotes may be messed up. And the following line

    aChart.Chart.SeriesCollection(n).formula = temp

    does not always put the correct formula in place, even if temp looks right.

    I've never used .ChartRefresh, but I suppose if the issue was making sure the chart was kept up to date, it would be more reliable than your two-line approach.


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Wednesday, February 23, 2011 12:28 AM
  • Thanks Jon,

    I checked that after the ...formula = temp executes, the formula string is exactly same as before. This approach was considered because I found on google that some people got issues with chart.refresh method which did not effectively refresh the chart.

    Wednesday, February 23, 2011 9:43 AM