Excel Charts + VBA: Changing y-axis Format

[TheRobsterUK]

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 t
change the currency from £’s to Euros and back by clicking a butto
which is linked to a macro. One of the main issues is that the butto
is on one sheet (lets call this Sheet1) and the chart object itself i
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 shee
as the button, but when the button is on a different sheet it doesn’
seem to work. Also I don’t want to have to select/activate the char
object to change the y-axis formatting, but I can’t seem to avoi
having to do this if I actually want to change anything about th
chart.

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


Code
-------------------
Sub Change_y-axis_to_Euros()

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

End Su
-------------------


I’ve tried several methods in order to change the chart object when i
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, the
I’ve tried:


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



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


Or just:


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



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

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


Code
-------------------
Sub Clear_Range_in_Sheet2

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

End Su
-------------------


This works without having to select Sheet2.

So, my questions are:

Is it possible to format a chart object without actually having t
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
-Ro

 

[Guest]

Hi

Chart is missing

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

Alain CROS

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:


Code:
--------------------
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:


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



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


Or just:


Code:
--------------------
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:


Code:
--------------------
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