How do I reference a Chart in an Excel VBA script Without The Chart Name

Question

I am creating an Excel VBA script to format and customize a chart. I want to be able to use the script on different charts in different workbooks, so I can't have the chart name like "Chart 1" in the code. I have seen ways to edit all the charts in a workbook, but I only want to edit the chart that I have clicked on in the workbook.

The first part of my VBA script, that was created by saving keystrokes, is shown below: As you can see, "Chart 1" is hard coded with each block of code. Is there a way that I can get away from using the chart name because I want to use this macro on different charts with different names.

Sub Format_Plot_Landscape()
ActiveChart.ChartArea.Select
With ActiveSheet.Shapes("Chart 1").Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With ActiveSheet.Shapes("Chart 1").Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveSheet.Shapes("Chart 1").Line.Visible = msoFalse
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
.
.
.

It has been a long time since I created a VBA script in Excel, I used to do it a lot when I wanted to apply specific consistent formatting to different charts. I dont remember the chart name being an issue. In this case even when I try to apply the VBA script to the same chart that I recorded the keystrokes on, I get the error "The item with the specified name wasn't found." on the following line of code:

With ActiveSheet.Shapes("Chart 1").Fill

I didn't change the name of the chart. I am running Office 2013 if that makes any difference.

Luuklag Luuklag · Accepted Answer · 2015-08-06T07:42:08

You want to replace your ActiveSheet.Shapes("Chart 1").Fill with something that references to the active chart.

Microsoft wrote an article about charts: https://msdn.microsoft.com/en-us/library/office/ff194426.aspx that states: "When a chart is the active object, you can use the ActiveChart property to refer to it."

From this I would guess you replace your ActiveSheet.Shapes("Chart 1").Fill with ActiveChart.ChartArea.Fill.

How do I reference a Chart in an Excel VBA script Without The Chart Name

1 Answers