Set xlPrimary and xlSecondary axis as percentage not working-CodePudding

Im not able to set xlPrimary AND xlSecondary axis as percentage. Only 1/2 is set as percentage and I can't understand why.

I tried this line of code to set my axis as percentage :

Set cht = Output.ChartObjects("Chart 1").Chart
    With cht
        .ChartArea.ClearContents
        .ChartType = xlColumnClustered
         xdata = EnregistrementsSecteurs
         ydata = EnregistrementsValeurs
            .SeriesCollection.NewSeries
            .SeriesCollection(1).XValues = xdata
            .SeriesCollection(1).Values = ydata
            .SeriesCollection(1).Name = "Rating"
        .Axes(xlPrimary).TickLabels.NumberFormat = "0.0%"
    End With

Set cht = Output.ChartObjects("Chart 1").Chart
    With cht
        .SeriesCollection.NewSeries
        .SeriesCollection(2).AxisGroup = xlSecondary
        .FullSeriesCollection(2).ChartType = xlLine
        .SeriesCollection(2).Values = TValues
        .SeriesCollection(2).Name = "Y"
        .SeriesCollection.NewSeries
        .SeriesCollection(3).AxisGroup = xlSecondary
        .FullSeriesCollection(3).ChartType = xlLine
        .SeriesCollection(3).Values = ThisWorkbook.Sheets("sheet").Range("D3:D12").Value
        .SeriesCollection(3).Name = "T"
        .Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"
    End With

It is working only If I have one scale. When I got 2 scale on same chart, only one axes is set to %.

The Output of the above code :

CodePudding user response：

You were close. Specify the axes Type:

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

CodePudding user response：

As Bigben mentioned, we need to apply data label before applying. I have checked the below code it is working for me. I have used the sample range.

 Sub test1()
    
    For Each ws In Worksheets("sheet1").Shapes
    
    Debug.Print ws.Name
    
    Dim EnregistrementsSecteurs As Range
    
    Dim EnregistrementsValeurs As Range
    
    Set EnregistrementsValeurs = Worksheets("sheet1").Range("B2:B6")
    Set EnregistrementsSecteurs = Worksheets("sheet1").Range("A2:A6")
    
    Next
    
    Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
        With cht
            .ChartArea.ClearContents
            .ChartType = xlColumnClustered
             xdata = EnregistrementsSecteurs
             ydata = EnregistrementsValeurs
                .SeriesCollection.NewSeries
                .SeriesCollection(1).XValues = xdata
                .SeriesCollection(1).Values = ydata
                .SeriesCollection(1).Name = "Rating"
                .SeriesCollection(1).ApplyDataLabels
                .SeriesCollection(1).DataLabels.NumberFormat = "0.0%"
        End With
    End Sub