Format Secondary Axis to PERCENT

Man.... sometimes I want to pull my hair out...

If sanitycheck > -1 Then
'Chart logic
Dim cdf_livepips As Object
Dim cfsrs As Series

'Top left draw-down chart
Set cdf_livepips = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
cdf_livepips.Select
cdf_livepips.Name = "cdf_livepips"
'apply series to active chart
Selection.Name = "cdf_livepips"
Set ns5 = ActiveChart.SeriesCollection.NewSeries
'Set ns6 = ActiveChart.SeriesCollection.NewSeries
ns5.XValues = dd_durationArray
ns5.Values = dd_arraycdf



'create the chart dimensions and style
With cdf_livepips
    .Chart.ChartType = xlXYScatterLines
    .Left = 750
    .Width = 700
    .Top = 350
    .Height = 300
    .Chart.SeriesCollection(1).MarkerStyle = xlLine
    .Chart.SeriesCollection(1).Format.Line.DashStyle = msoLineSysDash
    .Chart.SeriesCollection(1).Border.Weight = xlThin
    .Chart.SeriesCollection(1).MarkerStyle = 1
    .Chart.SetElement (msoElementLegendBottom)
    .Chart.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "hh"
    .Chart.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = Percent

...

Why does this not work!?

run-time error '-21476 bla bla bla....

Originally Posted by Olly

Try:

    .Chart.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "0.0%"

Thanks for your input, but nope... i tried this (screenshot: http://screencast.com/t/QZycmSAERUNq)

Exactly the same error...

Originally Posted by Olly

You do have a secondary category axis, right? You're not trying to change the secondary VALUE axis?

Maybe attach your workbook....

Hm... you know what, I think this maybe where im going wrong. I would attach my sheet but it's massive.

Literally this is everything I am doing here:

'------------------------------------------------------------------------------------------------------
'LIVE - Cumulative Density Function of Draw-down Duration
'------------------------------------------------------------------------------------------------------
If sanitycheck > -1 Then
'Chart logic
Dim cdf_livepips As Object
Dim cfsrs As Series

'Top left draw-down chart
Set cdf_livepips = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
cdf_livepips.Select
cdf_livepips.Name = "cdf_livepips"
'apply series to active chart
Selection.Name = "cdf_livepips"
Set ns5 = ActiveChart.SeriesCollection.NewSeries
ns5.XValues = dd_durationArray
ns5.Values = dd_arraycdf


'create the chart dimensions and style
With cdf_livepips
    .Chart.ChartType = xlXYScatterLines
    .Left = 750
    .Width = 700
    .Top = 350
    .Height = 300
    .Chart.SeriesCollection(1).MarkerStyle = xlLine
    .Chart.SeriesCollection(1).Format.Line.DashStyle = msoLineSysDash
    .Chart.SeriesCollection(1).Border.Weight = xlThin
    .Chart.SeriesCollection(1).MarkerStyle = 1
    .Chart.SetElement (msoElementLegendBottom)
    .Chart.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "DD HH HH"
    '.Chart.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "Percent"
    .Chart.Axes(xlCategory).TickLabelPosition = xlTickLabelPositionLow
    .Chart.HasTitle = True
    .Chart.ChartTitle.Characters.Text = whichPair & " - Live CDF Draw-down (pips)"
    .Chart.Axes(xlCategory).HasTitle = True
    .Chart.Axes(xlCategory).AxisTitle.Characters.Text = "Duration in drawdown to recovery"
    .Chart.Axes(xlValue).HasTitle = True
    .Chart.Axes(xlValue).AxisTitle.Characters.Text = "Frequency of Occurence"
    .Chart.ChartArea.Fill.Visible = False
    .Chart.PlotArea.Fill.Visible = False
    .Chart.ChartArea.Font.Color = RGB(255, 255, 255)
    .Chart.Axes(xlValue).TickLabels.Font.Size = 8
    .Chart.Axes(xlCategory).TickLabels.Font.Size = 8
    .Chart.ChartArea.Border.LineStyle = xlNone
    .Chart.HasLegend = False
    .Chart.Axes(xlValue).MaximumScale = 1
    .Chart.Axes(xlValue).MinimumScale = 0
End With
    
End If

Originally Posted by Olly

So is it a secondary x-axis, or the primary y-axis that you are trying to format as percent?

If it's the y-axis, then change that line to:

    .Chart.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "0.0%"

I know this may come across strong considering we're on a forum talking about VBA logic; but I love you...

One of those days where you're staring at an issue right under your nose.

On this topic:

.Chart.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "DD HH HH"

This works fine, however the underlying data is time duration in a fraction (e.g. 4208.28 - 4207.28). I want to illustrate MM:DD:HH however charts dont seem to like this?

Originally Posted by Olly

Glad that helped!!

If you want the x-axis labels formatted as "MM:DD:HH" then try:

.Chart.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mm"":""dd"":""hh"

Superb - thank you so much. I was nearly there. I was doing "MM:DD:HH". Not sure why this doesnt work this way?

Kudos to you!