Hi there,

Am having touble changing the ticklable number format based on the
contents of a cell(ie P for percent, N for number) using the change
event.

Here is what I have. I cant see any wrong with it, but any advice is
warmly welcome.

Private Sub Worksheet_Change(ByVal Target As Range)
graphtype = Range("C49").Value

With ActiveSheet.ChartObjects("Chart 2").Chart

Select Case graphtype
Case "P"
.Axis(xlValue).TickLabels.NumberFormat = "0%;[Red](0%)"
Case "N"
.Axis(xlValue).TickLabels.NumberFormat =
"#,##0;[Red](#,##0)"
End Select

End With

End Sub

regards,

david

Hi David

Change .Axis to .Axes

I would also embrace the code with something like this

If target.address = "$C$49" then
graphtype = target.Value
or simply
Select case target.Value
'code
End if

Regards,
Peter T

"Kained" wrote in message
ups.com...
Hi there,

Am having touble changing the ticklable number format based on the
contents of a cell(ie P for percent, N for number) using the change
event.

Here is what I have. I cant see any wrong with it, but any advice is
warmly welcome.

Private Sub Worksheet_Change(ByVal Target As Range)
graphtype = Range("C49").Value

With ActiveSheet.ChartObjects("Chart 2").Chart

Select Case graphtype
Case "P"
.Axis(xlValue).TickLabels.NumberFormat = "0%;[Red](0%)"
Case "N"
.Axis(xlValue).TickLabels.NumberFormat =
"#,##0;[Red](#,##0)"
End Select

End With

End Sub

regards,

david

thanks for your input Peter,

The way the sheet works is that a drop down list changes the data in
the source range of the graph(which are essentially a bunch of vlookups
based on the drop down list), so I dont need to know what the target
is, just the value of the cell at C49 (which is one of the vlookups
that can change between P and N)

I've also tried changed .axis to .axes but it hasnt resolved my
problem.

graphtype is returning what I want when I debug it either a P or a N
and the case statement is working. It just the

..Axis(xlValue).TickLabels.NumberFormat
or
..Axes(xlValue).TickLabels.NumberFormat

doesnt seem to work. its as if its read only or something?

Sorry if I seem nieve but why should I use the target.value and
target.address?

Regards,

david

Your code worked for me when I changed "Axis" to "Axes", though I ensure
there was a chart on the sheet named same as in the code.

Start the macro recorder, select your axis and apply one of your custom
number formats.

Sorry if I seem nieve but why should I use the target.value and
target.address?

It's normally best to do a quick check that the cell that's just changed is
the cell your interested in, ie $C$49". Otherwise your code will do
everything unnecessarily each time you change any non-related cell. in the
sheet.

You would only use "Select Case target value" if you've checked that target
is indeed the right cell. But stick with Select Case graphtype if you
prefer.

Regards,
Peter T

"Kained" wrote in message
ups.com...
thanks for your input Peter,

The way the sheet works is that a drop down list changes the data in
the source range of the graph(which are essentially a bunch of vlookups
based on the drop down list), so I dont need to know what the target
is, just the value of the cell at C49 (which is one of the vlookups
that can change between P and N)

I've also tried changed .axis to .axes but it hasnt resolved my
problem.

graphtype is returning what I want when I debug it either a P or a N
and the case statement is working. It just the

.Axis(xlValue).TickLabels.NumberFormat
or
.Axes(xlValue).TickLabels.NumberFormat

doesnt seem to work. its as if its read only or something?

Sorry if I seem nieve but why should I use the target.value and
target.address?

Regards,

david

thank again Peter,

ive also tried the code using chartobjects(1) for the named chart. I
still have the same problem

What is absolutely astonishing is that I can change the chart
successfully in the immediate window using...

ActiveSheet.ChartObjects("Chart
2").Chart.Axis(xlValue).TickLabels.NumberFormat = "0%;[Red](0%)"

but as soon as i try it in code, it doesnt want to know.

Could this be a hint at something I'm failing to take into account?

regards,

david

What is absolutely astonishing is that I can change the chart
successfully in the immediate window using...

ActiveSheet.ChartObjects("Chart
2").Chart.Axis(xlValue).TickLabels.NumberFormat = "0%;[Red](0%)"

Very astonishing because that fails for me. But change "Axis" to Axes" then
it works (assuming I've got "Chart 2" on the sheet).

In your Change event include the following -

Dim cht as chart
On error goto ErrH
Set cht = me.ChartObjects("Chart 2").Chart ' does this named chart exist
' code

Exit Sub
ErrH:
Stop
' press F8
Resume
End Sub

Where does Resume goto when you step through after the error

Regards,
Peter T

"Kained" wrote in message
oups.com...
thank again Peter,

ive also tried the code using chartobjects(1) for the named chart. I
still have the same problem

What is absolutely astonishing is that I can change the chart
successfully in the immediate window using...

ActiveSheet.ChartObjects("Chart
2").Chart.Axis(xlValue).TickLabels.NumberFormat = "0%;[Red](0%)"

but as soon as i try it in code, it doesnt want to know.

Could this be a hint at something I'm failing to take into account?

regards,

david