Line Weight in Excel Charts

Topic

New Reply

Hello…

How can I set the default line weight to “xlHairline” for a chart of up to 50 series being plotted with a line joining each value in a particular series.

Right now I am using Office 2010 and the line weight is shows as “xlThick” which hides other lines overlapping. and makes the plot unusable.

Thanks for your help.

FiL

Reply | Quote

Replies

Fil,

This was tested with 3 series but should work for the number of series you are working with. Replace 3 with the number of series. Replace “Chart 1” with the Chart name. If it is the only chart on the worksheet then leave it as is. Place in a standard module in the VB editor. Run the routine from the developer tab> Macros. The worksheet with the chart must be the Active sheet. I have included a sample with 2 routines: one to change the wt of the line of the series(s) to 1 and a second to a wt of 3 so you can toggle back and forth. Written in Excel 2010. Perhaps another member might know how to set a default but until then, this may be a work around.

HTH,
Maud

Code:

Sub SeriesWt1()
For I = 1 To 3 [COLOR=”#008000″]’CHANGE THE 3 TO THE NUMBER OF SERIES[/COLOR]
    ActiveSheet.ChartObjects(“Chart 1″).Activate
    ActiveChart.SeriesCollection(I).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
Next I
End Sub


Sub SeriesWt3()
For I = 1 To 3  [COLOR=”#008000”]’CHANGE THE 3 TO THE NUMBER OF SERIES[/COLOR]
    ActiveSheet.ChartObjects(“Chart 1”).Activate
    ActiveChart.SeriesCollection(I).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 3
    End With
Next I
End Sub

Reply | Quote

Hi Maud…

I had already tried the the approach you suggest, but it does not work once you start to “play around” with the Series Data. Like, I might start off with 50-series plot and later want to look at only four at a time to see how they compare.

I see that there is a HIDDEN table of DEFAULTS which needs to be dug out and then the line weight can be adjusted once and for all. Somehow…. the programmers decided it for themselves that the “millions” of users will be happy with what they selected as the default and did not allow those defaults to be accessed and changed. Or, they simply “forgot”.

For now, I can only see “xlHairline” in xlBorderWeight. But I need the actual line plot and not the Border to be changed…

Thanks again…

FiL

Reply | Quote

The code should continue to work as you “play around” but it would be helpful if you could elaborate on what adjustments you employ that make it no longer work. Perhaps, if you post a sample of your data, the code could be adjusted to meet your needs.
Have you considered a different approach of changing the color of the unwanted series to blend in with the background color of the chart or just make it not visible? The included sample shows how to make a series visible or hidden

HTH,
Maud

Reply | Quote

You cannot change the defaults but you can run a simple macro to make all series lines thin:

Code:

Sub allSeriesThin()
    Dim ser As Series
    For Each ser In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
        ser.Border.Weight = xlThin
    Next ser
End Sub

Reply | Quote

You cannot change the defaults…

This is just an example of a Crap design.

Thanks Rory, I will need to work out how to apply the code you have provided to my situation…

I have thousands of graph plots to examine each day and I just want something that I can set up and forget completely rather than have to apply a tweek to the plots each and every instant there is a change in the series data… or a new graph plot is created to over lay the previous ones.

Regards…

FiL

Reply | Quote