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 a

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

--
TheRobsterU

-----------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...nfo&userid=992
View this thread: http://www.excelforum.com/showthread.php?threadid=37695