VBA & Macro Problems

How to Fix Excel VBA Pareto Chart Name and Data Label Errors

Natalie Taylor Sep 30, 2026 869 views

Question details

The user encounters runtime errors in an Excel VBA macro when attempting to select a newly created Pareto chart using a hardcoded name and when trying to apply number formatting to the chart's data labels.

How to Fix Excel VBA Pareto Chart Name and Data Label Errors

Product: Microsoft Excel
Device & OS: not provided
Scenario: Executing a VBA macro to automate the creation and formatting of a Pareto chart from raw data.
Observed behavior: The macro throws the error 'The item with the specified name wasn't found' when selecting the chart (e.g., 'Chart 2'), and a separate syntax error occurs when trying to remove decimal places from the data labels.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options to edit your VBA code.

Solution 1Recommended

Use Dynamic Chart Referencing in VBA

Fix the chart selection error by dynamically capturing the newly created chart's object name instead of hardcoding a generic name like 'Chart 2'.

Excel auto-increments chart names every time a new chart is created. If you run your macro multiple times or modify charts, a hardcoded name will eventually fail, causing the 'item not found' error.

Open the VBA Editor

Press ALT + F11 to open the Microsoft Visual Basic for Applications window and locate the macro causing the error.

Declare a ChartObject Variable

Add 'Dim myChart As ChartObject' to your VBA script to prepare a variable for your chart.

Assign the Chart Object

Immediately after the line that creates the Pareto chart, assign it to your variable by typing 'Set myChart = ActiveSheet.ChartObjects(1)'.

Select the Chart Dynamically

Replace the hardcoded selection line with 'ActiveSheet.Shapes.Range(Array(myChart.Name)).Select' to successfully select the chart regardless of its generated name.

Best Practice: Using variables for chart objects ensures your macro remains robust and error-free even if the underlying worksheet data or shape index changes.

Solution 2

Fix Data Label Number Formatting Syntax

Correct the VBA syntax used to modify the decimal places of the Pareto chart's data labels.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

WPS Office offers a robust, lightweight, and free alternative to Microsoft Office. It provides excellent compatibility with Excel formats and includes powerful built-in charting tools to easily visualize your data without complex programming.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbooks.
3. Create Charts Easily: Navigate to the Insert tab to instantly create charts from your data using intuitive graphical menus.

Fully compatible with Microsoft Excel formats, including .xlsx and .xlsm.Familiar user interface requires no learning curve.Built-in charting tools to easily create complex charts.Lightweight design that runs smoothly on almost any system.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel VBA say 'The item with the specified name wasn't found' when selecting a chart?

This happens because Excel automatically increments chart names (e.g., Chart 1, Chart 2) upon creation. If your VBA script hardcodes a specific name, it will fail when Excel generates a different name for the new chart.

How do I remove decimal places from data labels using VBA?

You can change the number format by modifying the DataLabels.NumberFormatLocal property. Setting it to "#,##0" will successfully remove decimals and apply a thousands separator.

Are Pareto charts supported in all versions of Excel via VBA?

No. Native Pareto charts (using the xlPareto chart type) were introduced in Excel 2016. Older versions do not support the AddChart2 method with xlPareto and require you to build a combination column/line chart manually.

Excel & Data Problems