Secondary and Primary Axis are of Same Level Using VBA

Question

I'm trying to create a pivot chart. But there are couple of series which are negative. So I want to shift them to negative axis and try to make the level of both the primary and secondary axis to be same.

I'm using the following code. But this is not helping me to format the axis properly.

Sub createChart()

On Error Resume Next

ActiveChart.Delete
Application.ScreenUpdating = False

Dim myPT As PivotTable

Dim primaryMax As Integer
Dim primaryMin As Integer
Dim secondaryMax As Integer
Dim secondaryMin As Integer
Dim max As Integer
Dim min As Integer

Set myPT = ActiveSheet.PivotTables("CPivotTable")
Set mySheet = Sheets("PivotTable")
myPT.PivotSelect ("")

Charts.Add

ActiveChart.Location Where:=xlLocationAsObject, _
Name:=myPT.Parent.Name
ActiveChart.Parent.Left = Range("D8").Left
ActiveChart.Parent.Top = Range("D8").Top

ActiveChart.ChartType = xlArea



Set ch = ActiveSheet.ChartObjects(1).Chart
For Each ser In ch.SeriesCollection
    If ser.Name Like "Var2" Or ser.Name Like "Var3" Then
        ser.AxisGroup = xlSecondary
    End If
 Next

 With mySheet
        With .ChartObjects(1).Chart.Axes(xlValue)
            primaryMin = .MinimumScale
            primaryMax = .MaximumScale
        End With
        With .ChartObjects(1).Chart.Axes(xlValue, xlSecondary)
            secondaryMin = .MinimumScale
            secondaryMax = .MaximumScale
        End With

        If primaryMax > secondaryMax Then
            max = primaryMax
        Else
            max = secondaryMax
        End If

        If primaryMin < secondaryMin Then
            min = primaryMin
        Else
            min = secondaryMin
        End If
        With .ChartObjects(1).Chart.Axes(xlValue)
            primaryMin = min
            primaryMax = max
        End With
        With .ChartObjects(1).Chart.Axes(xlValue, xlSecondary)
            secondaryMin = min
            secondaryMax = max
        End With

    End With



Range("A1").Select
Application.ScreenUpdating = True



End Sub

Beta Beta · Accepted Answer · 2017-05-07T17:51:29

I used the following code to solve my problem. Putting it here, in case somebody else have the same problem.

Dim PriMax, PriMin
Dim SecMax, SecMin

ActiveSheet.ChartObjects(1).Activate
ActiveChart.Axes(xlValue, xlPrimary).Select

PriMax = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
PriMin = ActiveChart.Axes(xlValue, xlPrimary).MinimumScale
SecMin = ActiveChart.Axes(xlValue, xlSecondary).MinimumScale
SecMax = ActiveChart.Axes(xlValue, xlSecondary).MaximumScale

PriMin = SecMin
SecMax = PriMax

ActiveChart.Axes(xlValue, xlPrimary).MaximumScale = PriMax
ActiveChart.Axes(xlValue, xlPrimary).MinimumScale = PriMin
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = SecMax
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = SecMin

Secondary and Primary Axis are of Same Level Using VBA

1 Answers