6

Is there a way to edit the weight of all lines at once on an Excel line chart?

I have a graph with about 50 data series on it, and it's too much to go through all of them and change the weights individually, and it's hard to see the data if the lines were thinner, it'd be a bit easier). I can't see a way to multi-select them, which seems a bit awful.

Edit: I eventually got it working with the following code (after learning a bit of VB). I doesn't seem to help readability of the graph much though! Chris helped me sort it out, so I'll give him the tick.

Sub onepxlines()
        With Selection
            MsgBox ("Selection: " & TypeName(Selection))
            If TypeName(Selection) = "ChartArea" Then
                Dim area As ChartArea
                Set area = Selection

                MsgBox ("Area parent: " & TypeName(area.Parent))

                If TypeName(area.Parent) = "Chart" Then
                    Dim chart As chart
                    Set chart = area.Parent
                    Dim srs As Series

                    For Each srs In chart.SeriesCollection
                        srs.Format.Line.Weight = 1.5
                    Next
                End If
            End If
        End With
End Sub
7

Probably easiest to write a quick macro, somthing like

Sub SetWeights()
    Dim srs As Series
    For Each srs In ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection
        srs.Format.Line.Weight = 0.75
    Next
End Sub
  • Cheers, I'll try this out :) – Chris Dennett Nov 16 '11 at 14:51
5

This is a more copy-and-paste compatible version of Chris's answer:

Sub SetWeights()
    Dim srs As Series
    For Each srs In ActiveChart.SeriesCollection
        srs.Format.Line.Weight = 0.25
    Next
End Sub

Just select the chart and run the macro.

3

Enhancement: create a VB variable (LineWT) that points to a cell in the spreadsheet. Then instead of "Format.Line.Weight = 0.25" change 0.25 to LineWT. Then you can change the line weight by changing it on spreadsheet and clicking the macro button. You can use similar code to set X/Y Axes names with pointers to the spreadsheet.

2

This worked perfectly for me. I used this code to have it edit all lines in all charts on all sheets of a workbook

Sub ChangeLineType()

'This macro will print all embedded charts in the active workbook
'
    Application.ScreenUpdating = False
    Dim Sht As Object
    Dim Cht As ChartObject
    For Each Sht In ActiveWorkbook.Sheets
        For Each Cht In Sht.ChartObjects
            Cht.Activate
            ActiveChart.ChartArea.Select
            'ActiveWindow.SelectedSheets.PrintOut
                Dim srs As Series
                For Each srs In ActiveChart.SeriesCollection
                srs.Format.Line.Weight = 0.25
                Next
        Next
    Next
End Sub
0

I don't think you can multi-select lines. The simplest way is probably to change one and then to select each on in turn use Ctrl-Y to go through and re-do the line weight change.

  • There's got to be something better than this. Maybe something like a custom chart style? – Chris Dennett Nov 16 '11 at 1:54
0

This code changes the line width of all charts in the active spreadsheet including chart sheets:

' Sets the line thickness for all charts in the active spreadsheet.
Sub ChangeLineType()
    Dim Cht As Chart
    Dim Chts As New Collection
    Dim Sht As Object   ' Can be Chart or WorkSheet

    For Each Sht In ActiveWorkbook.Sheets
        If TypeName(Sht) = "Chart" Then
            Chts.Add Sht
        Else
            For Each Cht In Sht.ChartObjects
                Chts.Add Cht
            Next
        End If
    Next

    Application.ScreenUpdating = False
    For Each Cht In Chts
        Dim Srs As Series
        For Each Srs In Cht.SeriesCollection
            Srs.Format.Line.Weight = 0.25
        Next Srs
    Next Cht
End Sub

(Adapted from David Markman's answer.)

0

In the Home tab, Font section, select the Borders drop-down. Pick your required line style or color. A pencil will appear. Instead of using the pencil to click on each line you want to change, go to the corner of the grid of cells you want to change, and hold down the CNTRL key. Drag the pencil over the required grid of cells, and all will change to the style or color you have selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.