[Excel VBA] How to Change Scatter Plot Line Thickness Based on Cell Value

Background

When visualizing data using multiple scatter plots in Excel, manually adjusting the line thickness for each chart individually can be tedious.

It is often desirable to change the line thickness dynamically. This article introduces a mechanism to control the line width of all charts collectively using VBA.

Desired Specifications

• There are multiple scatter plots on the Excel sheet.
• The numerical value entered in cell P3 is used as the “Line Thickness.”
• A button (shape) is placed next to the cell. When clicked, the line thickness of all graphs is automatically updated to the value in P3.

Implementation Code (VBA)

Paste the following code into a Standard Module.

Sub line_wide()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim ser As Series
    Dim lineWidth As Double

    ' Get the active sheet
    Set ws = ActiveSheet

    ' Get the value from cell P3 (Line Thickness)
    lineWidth = ws.Range("P3").Value

    ' Loop through all chart objects in the worksheet
    For Each cht In ws.ChartObjects
        ' Process every series in each chart
        For Each ser In cht.Chart.SeriesCollection
            ' Apply the line weight
            ser.Format.Line.Weight = lineWidth
        Next ser
    Next cht
End Sub

How to Use

1. Enter a numerical value for the line thickness (e.g., 3 or 2.5) in cell P3.
2. Insert a Shape (e.g., a rectangle labeled “Run”) on the sheet.
3. Right-click the shape and select Assign Macro.
4. Select line_wide and click OK.
5. Click the execution button to update the line thickness of all graphs to the specified value.

Important Notes

• Input Validation: Be careful as entering non-numeric values in cell P3 may cause an error. Using IsNumeric() or On Error Resume Next can provide error handling if necessary.
• Scope: This process applies the thickness uniformly to SeriesCollection (all series).
• Units: Format.Line.Weight is set in points (e.g., 1, 2.5).

Tips for Application

• It is possible to change the thickness for each series individually by linking them to multiple corresponding cells.
• Using Chart.FullSeriesCollection ensures that all series in the chart are covered, which is reliable for complex charts.

Summary

• Use Format.Line.Weight to control graph line thickness via VBA.
• Linking variables to cell input allows for a highly usable UI.
• This technique is effective for efficiently adjusting the appearance of multiple graphs.

I can provide the modified code to handle non-numeric input errors if that would be useful for your implementation.