Ticklabel problem

[Kained]

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

 

[Peter T]

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]

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

 

[Peter T]

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]

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

 

[Peter T]

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

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

 

[Kained]

thanks Peter,

however it doesnt 'stop'. The code doesnt produce an error.

I also tried wrapping it using a with activesheet.chartobjects(1).chart
and removing redundancies... but still no luck

I also tried this way ... after making sure only 1 chart existed... (
verified by debug.pring chartobjects.count)

I've also set up a watch for activesheet.chartobjects and verified the
named object exists.

Select Case Cells(49, "C").Value
Case "P"

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

Case "N"

ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).TickLabels.NumberFormat
= "#,##0;[Red](#,##0)"
End Select

I still get the same problem...
I cut and past the above numberformat statements into the immediate
window... and sure enough, it works.

Am gonna have no hair left soon... :\

There has got to be something glaringly obvious that am overlooking or
some rediculas VB quirk (am on excel 2002, VBA 6.3 using W2K )

regards,

david

 

[Kained]

Peter,

Ive stumbled on a related problem... I have this function in a
module...
When i use it on the same sheet the code we were discussing wont work.
If i delete the function from the sheet, the chart code works fine.

Am not sure why... the code simply returns the first row on a filtered
sheet, s being the data sheet, col being the column.


Private Function firstFiltered(s As String, col As Integer) As String

On Error Resume Next
Application.Volatile

Dim r, fr, cr As Integer
Dim rng As Range

r = 0
With Sheets(s)
Set rng = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For Each c In rng
cr = c.Row
'identify first row
If r = 0 Then
fr = c.Row
r = r + 1
End If
If c.RowHeight > 0 And c.Row <> fr Then
firstFiltered = .Cells(cr, col).Value
Exit For
End If
Next
End With
End Function

am I leeking data somewhere? not releasing memory?

regards,
-david

 

[Peter T]

Best retain whatever strands of hair you've still got!

Is the event actually firing, put a break on the first line and step through
with F8

Include following before "Select Case" and again after "End Select"

Msgbox Me.ChartObjects(1).Chart.Axes(xlValue).TickLabels.NumberFormat

or instead of msgbox do debug.print

and toggle C:49 "P" <> "N"

Regards,
Peter T

 

[Peter T]

Not sure I like that function at all but even with it on the sheet stiil the
event stuff works. But I'd look at that function again

on error goto errH
With Sheets(s)
if .AutoFilter is nothing then ' no autofilter
??
else
' don't think specialcells help in a UDF
For Each r In .AutoFilter.Range.Rows
' skip first row
If r.Height And n > 0 Then
x = r.Row
Exit For
End If
n = n + 1
Next
end with
errH:

BTW
Dim r, fr, cr As Integer
You have declared r & fr as variants. cr as Integer would fail with over 32k
rows

Regards,
Peter T

 

[Kained]

your firstfiltered is much more elegant, thank you.

I think i solved the problem, kind of... I removed the
application.volitile statement from the function and my charts work
perfectly.

There must have been some kind of recursive recalculation going on as
the charts were driven by data dirrived from the firstfiltered
function.???

I think a better option is just to inspect the autofilter criteria for
a given column.

i need to look at this application.volitile problem a little more as
idealy i need this function updating and retrieving a name as and when
the data is filtered differently on my data sheet.

ive a hair or two left to go at yet....
-david