Excel 2013 Charts - Change in Line Width Not Retained After Save

[KAWill]

I'm using Excel 2013 and Windows 7.

A chart displays a number of different data series as lines on a normal xy line chart with primary and secondary axis both in use. The "weight" of the lines is 1 point. I can change the weight to 1.5 points and the wider line displays correctly. However, if I save the spreadsheet and then open it again, the line width reverts to 1 point.

Any thoughts on the cause? If I use 2 points instead the problem disappears. The spreadsheet is xls format running in compatibility mode.

 

[pbornemeier]

I cannot replicate the problem. Do you have any code running in that workbook, or is this just after manual changes to the graph series format?

 

[KAWill]

I cannot replicate the problem. Do you have any code running in that workbook, or is this just after manual changes to the graph series format?

There is no code running. I have quite a few spreadsheets from Excel 2003 and the problem seems to occur with only some of them. I converted one of the problem spreadsheets to xlsx format and that seems to resolve the problem.

The odd thing is that I think the chart line widths come up with no width specified at all in the converted spreadsheet. It seems like Excel is adjusting the width automatically. I can see that when some action changes the displayed size of the spreadsheet. It also seems that changing line width is very time consuming when compared to the effort required in Excel 2003.

Thanks for the help.

 

[pbornemeier]

I use this code to give multiple graphs the same format. Perhaps you can modify it to suit your needs.

Sub SetLineChartLinesThick()

    Dim intChartLineCount As Integer
    Dim intX As Integer
    Dim booErrorObjectNotSet As Boolean
    
    On Error GoTo ErrorHandler
    
    'Verify Chart is selected
    booErrorObjectNotSet = True
    'If next line raises error 91, chart not selected
    intX = ActiveChart.ChartType
    booErrorObjectNotSet = False
    
    Select Case ActiveChart.ChartType
    Case 63 To 67, 4
        'Line Chart Type is selected
        
        intChartLineCount = ActiveChart.SeriesCollection.count
        Application.ScreenUpdating = False
        For intX = 1 To intChartLineCount
            ActiveChart.SeriesCollection(intX).Select
            With Selection.Border
                '.ColorIndex = 57
                .Weight = xlMedium
                .LineStyle = xlContinuous
            End With
            With Selection
                .MarkerBackgroundColorIndex = xlAutomatic
                .MarkerForegroundColorIndex = xlAutomatic
                .MarkerStyle = xlAutomatic
                .Smooth = False
                .MarkerSize = 7
                .Shadow = False
            End With
        Next
        
    Case Else
    'Don't act on other types of charts
    
    End Select
    
    ActiveChart.Deselect
    Application.ScreenUpdating = True
    
    GoTo End_Sub
ErrorHandler:
    
    Select Case Err.Number
    Case 91
        If booErrorObjectNotSet Then
            MsgBox "A chart is not selected", , ""
        End If
        Exit Sub
    Case Else
        Debug.Print Err.Number, Err.Description
    End Select

End_Sub:
End Sub

 

[KAWill]

I use this code to give multiple graphs the same format. Perhaps you can modify it to suit your needs.

Thanks for the help once again. I may have found an indirect solution to the problem by converting the xls file to xlsm and then back again to xls format (Excel 2003). The chart line widths now work as expected. I wonder if some anomaly was repaired in the process. The problem was only seen in one or two of quite a few spreadsheets.

One interesting thing is that the final xls file is slightly larger than the original. These spreadsheets are quite large and contain stock market index data going back to 1986 and many charts showing different technical indicators. The original xls file is 17341 KB while the xlsm version is 8212 KB. After converting back to xls the file becomes 17376 KB.

I read that Excel 2013 uses a totally different format to represent the data which explains the size difference. Using 7-zip it is also interesting that the xls file compresses significantly while the xlsm file does not. Here is the data.

Original xls:        17341 KB  Compressed 5558 KB
Converted to xlsm:    8212 KB  Compressed 7339 KB
Converted to xls:    17376 KB  Compressed 5561 KB

 

[pbornemeier]

You may also want to experiment with the .xlsb format. It will be smaller in most cases, and load time shorter, but it won't support any Ribbon modification.
XLSB vs. XLSM formats

 

[KAWill]

You may also want to experiment with the .xlsb format. It will be smaller in most cases, and load time shorter, but it won't support any Ribbon modification.

Thanks for the note on the .xlsb format which I had never looked at until now. My spreadsheets are definitely the smallest in that format.

However, in terms of the time to open, xls format seems to be the fastest here. That makes me wonder if there is more overhead in decompressing the newer formats (xlsx, xlsm, xlsb) as compared with the much larger xls files.