*Resolved* Excel Charts + VBA: Changing y-axis Format

[TheRobster]

I’m trying to use a VBA macro to change the number formatting (currency) on the y-axis of an Excel chart object. Basically I need to be able to change the currency from £’s to Euros and back by clicking a button which is linked to a macro. One of the main issues is that the button is on one sheet (lets call this Sheet1) and the chart object itself is on another sheet (lets call this Sheet2).

I am having some problems getting this to work.

I can change the y-axis when I have the chart object on the same sheet as the button, but when the button is on a different sheet it doesn’t seem to work. Also I don’t want to have to select/activate the chart object to change the y-axis formatting, but I can’t seem to avoid having to do this if I actually want to change anything about the chart.

Basically this is the (recorded) macro if the button that activates it is on the same sheet as the chart:

Sub Change_y-axis_to_Euros()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "[$€-1809]#,##0;[Red]-[$€-1809]#,##0"

End Sub

I’ve tried several methods in order to change the chart object when it is on a different worksheet to the button that runs the macro e.g. suppose the button is on Sheet1 and the chart object on Sheet2, then I’ve tried:

With Sheets(“sheet2”)
.ChartObjects("Chart 1").Axes(xlValue).TickLabels.NumberFormat = _
 "[$€- 1809]#,##0;[Red]-[$€-1809]#,##0"
End With

With ChartObjects(“Chart 1”)
	.Axes(xlValue).TickLabels.NumberFormat = "[$€- 1809]#,##0;[Red]-[$€-1809]#,##0"
End With

Or just:

ChartObjects("Chart 1").TickLabels.NumberFormat = "[$€- 1809]#,##0;[Red]-[$€-1809]#,##0"

Plus many variations on these themes, but none of them work. I usually get an error along the lines of “Sub or Function not Defined” or “Runtime error 1004: unable to get ChartObjects property of the worksheets class”.

I just find it a bit strange that I can’t change a chart object unless the worksheet that contains the chart is actually active. I know you can edit ranges without having to select either the worksheet they are on or the ranges themselves e.g. if I am on Sheet1 and run the following macro:

Sub Clear_Range_in_Sheet2

With Sheets(“Sheet2”)
      .Range(“Test_Range”).Clear
End With

End Sub

This works without having to select Sheet2.

So, my questions are:

Is it possible to format a chart object without actually having to select the chart OR the worksheet it is contained within?

If so how would I do this?

If not is there a “work-around” solution?

Many thanks
-Rob