VBA & Macro Problems

How to Dynamically Change Excel Chart Line Width from a Cell Value using VBA

WPS Content Manager Sep 27, 2026 869 views

Question details

The user wants to dynamically adjust the line thickness of a chart series in a spreadsheet based on a specific numerical value entered in a worksheet cell.

How to Dynamically Change Excel Chart Line Width from a Cell Value

Product: Excel
Device & OS: not provided
Scenario: Automating chart formatting so that visual elements update instantly based on worksheet data inputs.
Observed behavior: Excel does not have a built-in formula feature to link chart line width to a cell value, requiring a VBA workaround.

Before you start

Ensure you have saved your workbook as an Excel Macro-Enabled Workbook (.xlsm) and have enabled the Developer tab in your ribbon to access the VBA editor.

Solution 1Recommended

Use a VBA Macro to Update Chart Line Width

Create a VBA macro that reads a specified cell's value and applies it to the thickness property of a selected chart series.

Since standard spreadsheet functions do not support formatting properties inside charts, a VBA script is the most reliable way to link a cell value to a chart's line width. This script will identify the target chart, read the desired thickness from a cell, and apply it to the line.

Open the VBA Editor

Navigate to the Developer tab on the top ribbon and click 'Visual Basic', or simply press Alt + F11 on your keyboard.

Insert a New Module

In the VBA editor, right-click on your workbook name in the Project Explorer panel on the left, hover over 'Insert', and select 'Module'.

Enter the Macro Code

Type a macro that targets your chart and cell. For example: ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).Format.Line.Weight = ActiveSheet.Range("A1").Value

Run or Assign the Macro

Close the VBA editor. You can run this macro from the Developer tab > Macros, or insert a Button (Form Control) on your worksheet and assign this macro to it for quick one-click updates.

Use a VBA Macro to Update Chart Line Width

Verify Chart and Cell Names: Make sure to adjust the chart name (e.g., 'Chart 1') and cell reference (e.g., 'A1') in your VBA code to match your actual workbook structure. You can find the chart name in the Name Box when the chart is selected.

Powerful Spreadsheet Tool

Automate Chart Formatting with WPS Spreadsheet

WPS Spreadsheet offers robust charting tools and macro support, allowing you to create dynamic, professional charts with ease while maintaining full compatibility with Microsoft Excel files.

1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook containing the chart.
2. Access the VBA Editor: Go to the 'Developer' tab and click on 'VBA Editor' to open the scripting environment.
3. Apply the formatting macro: Insert a module, paste your chart line width macro, and link it to a worksheet button.
4. Update your chart dynamically: Change the target cell value and click the assigned button to instantly update your chart's line width in WPS.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) file formats.Supports VBA macros for advanced chart and data automation.Lightweight, fast, and runs smoothly on all devices.Intuitive tabbed interface for effortlessly managing multiple workbooks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I change chart line width dynamically without VBA?

No, Excel does not currently support linking chart formatting properties (like line width or colors) directly to a cell value using standard formulas. A VBA macro is required to bridge this gap.

How do I find the exact name of my chart for the VBA code?

Click on the chart in your worksheet to select it. Look at the Name Box located to the left of the formula bar at the top of the screen. You will see the chart's exact name, such as 'Chart 1' or 'Chart 2'.

Why isn't my VBA macro updating the chart line?

Ensure that the macro references the correct active sheet, chart name, and series index. Additionally, check that macros are enabled in your Trust Center settings and that the target cell contains a valid positive number for the line weight.

Will this macro run automatically when I change the cell value?

By default, standard macros require you to run them manually or via a button click. To make it automatic, you must place the VBA code inside a Worksheet_Change event in the specific sheet's VBA module, targeting the specific cell address.

Excel & Data Problems