Thread: Toggling Secondary & Primary Axises

Hi all.

I am working on a chart that displays a series collection on the 2nd axis if it is present, the code:

If Me.chkBxEsc.Value = True Then
        For SeriesCnt = 1 To Chrt.SeriesCollection.Count
            ChrtName = Chrt.SeriesCollection(SeriesCnt).Name
            If ChrtName = "Composite Escalation Rate" Then
                With Chrt.SeriesCollection(SeriesCnt)
                    .Name = ChrtName & " (Right Side %Change)"
                    .AxisGroup = 2
                    .MarkerStyle = 1
                    .MarkerSize = 5
                End With
            End If
        Next SeriesCnt

This worked great the first time but now all values are coming up on the 2nd axis even when the name "Composite Escalation Rate" is not present. I have a procedure dedicated to chart display but I am lost in it but here it is

Sub ChartProcedure()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''This procedure loads the chart with user selections'''
'''and also changes the chart layout if the user has  '''
'''selected the escalator option.                     '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Chrt As Chart
    Dim ChrtRng As Range
    Dim ChrtName As String
    Dim SeriesCnt As Integer, x As Integer
    Dim tmChoice As Integer
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Set the Chrt variable to represent the active '''
    '''chart on the worksheet "Charts"               '''
    Set Chrt = WsChrt.ChartObjects("Chart 83").Chart
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Set the ChrtRng variable which is used to     '''
    '''set the chart source data.                    '''
    Set ChrtRng = WsChrt.Cells(30, 1).CurrentRegion
    ChrtRng.Cells.Borders.LineStyle = xlContinuous
    Chrt.SetSourceData Source:=ChrtRng.Cells
    '''If the Escalation Check box is selected, find '''
    '''the series
    If Me.chkBxEsc.Value = True Then
        For SeriesCnt = 1 To Chrt.SeriesCollection.Count
            ChrtName = Chrt.SeriesCollection(SeriesCnt).Name
            If ChrtName = "Composite Escalation Rate" Then
                With Chrt.SeriesCollection(SeriesCnt)
                    .Name = ChrtName & " (Right Side %Change)"
                    .AxisGroup = 2
                    .MarkerStyle = 1
                    .MarkerSize = 5
                End With
            End If
        Next SeriesCnt
    Else
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        '''This is where if the chkBxEsc.Value = False then'''
        '''the values should be on the left side of the    '''
        '''but it is not working out that way.             '''
        For SeriesCnt = 1 To Chrt.SeriesCollection.Count
            With Chrt.SeriesCollection(SeriesCnt)
                .AxisGroup = 1
                .MarkerStyle = 0
            End With
            With Chrt
                .SetElement (msoElementPrimaryCategoryAxisShow)
            End With
        Next SeriesCnt
    End If

    If Me.fMth.Visible = True Then tmChoice = 1
    If Me.fQtr.Visible = True Then tmChoice = 2
    If Me.fAnl.Visible = True Then tmChoice = 3
    Select Case tmChoice
    Case 1
        With WsChrt.ChartObjects("Chart 83").Chart
            .HasTitle = True
            .ChartTitle.Text = "Monthly IPI"
            .PlotBy = xlRows
        End With
    Case 2
        With WsChrt.ChartObjects("Chart 83").Chart
            .HasTitle = True
            .ChartTitle.Text = "Quarterly IPI"
            .PlotBy = xlRows
        End With
    Case 3
        With WsChrt.ChartObjects("Chart 83").Chart
            .HasTitle = True
            .ChartTitle.Text = "Annual IPI"
            .PlotBy = xlRows
        End With
    End Select
End Sub

The following code was an attempt to solve the problem but it did nothing

With Chrt
    .SetElement (msoElementPrimaryCategoryAxisShow)
End With

I can upload the workbook but if someone has an idea of what I am trying to accomplish through the code I have provided I would really appreciate it. I only want the one series collection to display on the right side or 2nd axis if it is present, otherwise, display everything else on the left. Any ideas?

Because my file is quite large, I'm going to supply a picture (knowing how you all love pictures ).The right side Y-Axis should be on the left side and the left side Y-Axis should be on the right if and only if the Composite Escalation Rate is a part of the chart, otherwise the right Y-Axis should not be there. This has been a struggle for me and the books that I have don't go into VBA and charting with too much detail.Axis.JPG

Try using the AxisGroup setting

    Dim chtTemp As Chart
    Dim objSeries As Series
    
    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    
    For Each objSeries In chtTemp.SeriesCollection
        If InStr(1, objSeries.Name, "(on right)", vbTextCompare) > 0 Then
            objSeries.AxisGroup = 2
        Else
            objSeries.AxisGroup = 1
        End If
    Next