How to link macro to chart when sheet is copied?

[Kelvin Stott]

Hi All,

I have a protected worksheet that includes several embedded charts, one of which I enable users to update with the following macro:

Private Sub Rectangle55_Click()
    With ActiveSheet
        .Calculate
        .Unprotect Password:="mypassword"
        .ChartObjects("[B]Chart 47[/B]").Chart.Axes(xlValue).CrossesAt = Range("Q497")
        .Protect Password:="mypassword", _
            UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True
        .EnableOutlining = True
    End With
End Sub

I would also like to enable users to copy that sheet, including all the charts, and to update that one particular chart with the macro. However the macro refers to specific chart reference "Chart 47", which changes when I copy the sheet, so that I get the following error:

Run-time error '1004':
Unable to get the ChartObjects property of the Worksheet class

Is there any way I can assign some alternative label to that one chart in the original sheet, which will be retained when the sheet is copied so that the macro can identify the appropriate chart in each sheet?

Please help!

 

[Rasm]

I am not sure that I understand.

But if the problem is that the chart has a new name then you can read the names like the code below illustrates - obvs you have to implement the way you want. But as I said - not sure if it is the name that is the problem.

Private Sub CommandButton1_Click()
    NumCharts = ActiveSheet.ChartObjects.Count
    For i = 1 To NumCharts
        Astr = ActiveSheet.ChartObjects(i).Name
    Next i
End Sub

 

[Kelvin Stott]

Thanks, but I'm not sure we're talking about the same issue. Currently my macro relates to a chart reference "Chart 47", but a new chart reference is automatically created when the chart is copied, so my macro does not recognize the new chart. Essentially I'm looking to label the chart so that any copies of that chart can also be recognized by my macro. Does that make sense?

 

[AlphaFrog]

This references the 1st chart on the active sheet irregardless of its name. Would that work for you?

.ChartObjects([COLOR="Red"]1[/COLOR]).Chart.Axes(xlValue).CrossesAt = Range("Q497")

That's what Rasm was suggesting. Count the charts on the copied sheet and then reference each one by index number.

 

[Kelvin Stott]

Ah, right, thanks ... but how are the charts numbered on each sheet? Is it the order they appear from top to bottom, left to right, or when they were created, or what? If I knew, I could just determine the right number directly.

 

[AlphaFrog]

The index number is the order they were created (I think).

 

[Kelvin Stott]

OK, I'll give it a try, thanks!

 

[Kelvin Stott]

Yep, this worked perfectly, thanks!

 

[Rasm]

It is the order they are created.