Take the 2-minute tour ×

I have an Excel 2007 workbook with multiple Charts on one worksheet (stacked bar chart and one series as (invisible) line for the totals). Depending on a cell selected by the user, the bar charts should have connectors or not.

For this, I have a small VBa routine trigged by the Worksheet_Change event that loops over the charts and either sets the connectors using objChart.SetElement msoElementLineSeriesLine or objChart.SetElement msoElementLineNone respectively.

This generally works in 90% of the cases. However, sometimes - at some, varying charts, objChart.SetElement results in a error -2147467259 The specified dimension is not valid for the current chart type. - and breaks the chart, so it looks like this:

Example of corrupt chart

The way the fix this after the error occurred is to reopen the file - even if saved after the error occurred. Therefore it seems to be some kind of display problem.

share|improve this question
If you were to just close and reopen the file, does it automatically correct itself? Where is this file saved? –  Dave Rook Jan 10 '13 at 9:37
So far that was the behavior. However, since a few hours it stays corrupted. :-( The file is stored on my local harddrive. And stranger still: the more I play around with it (opening and saving it on different machine/different format (.xlsb/.xlsm)) more charts get corrupted - without other changes! :-( –  Peter Albert Jan 10 '13 at 9:56
Something very very odd is going on. There are third party Excel Repair Tools which may help (not used before) excelrepairtool.com –  Dave Rook Jan 10 '13 at 9:57
Okay, managed to drill down the problem quite a bit further, updated the question to reflect those findings. –  Peter Albert Jan 10 '13 at 22:24
If it is a coding issue then you would need to share it. Does the issue only occur on the worksheet change event or when excel opens? –  Dave Rook Jan 11 '13 at 5:50
add comment

1 Answer

I think SetElement msoElementLineSeriesLine isn't as robust as is the old syntax, ChartGroups(i).HasSeriesLines

msoElementLineSeriesLine seems not to understand chart groups, axis groups, and so forth. Perhaps if the first series it notices is not a stacked bar or column, it chokes.

Sub AddSeriesLines()
  Dim cg As ChartGroup
  For Each cg In ActiveChart.ChartGroups
    Select Case cg.SeriesCollection(1).ChartType
      Case xlColumnStacked, xlColumnStacked100, xlBarStacked, xlBarStacked100
        cg.HasSeriesLines = True ' False to remove
    End Select
End Sub

Funny thing, the chart engine redesign of 2007/2010 still had a means to add series lines in the user interface, but I can't find it in 2013 (though as I said, the above code works fine).

share|improve this answer
add comment

Your Answer


