Hi

Chart is missing

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

Alain CROS

"TheRobsterUK" wrote:


Im 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 doesnt
seem to work. Also I dont want to have to select/activate the chart
object to change the y-axis formatting, but I cant 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
--------------------


Ive 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
Ive 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 cant 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 a

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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=376951