Thread: How to keep the LineType of One Series Collection While Other Collection Types Change

Hi all and Andy if you are looking,

I have left and right toggle arrows that toggle my chart types. Under certain conditions created by the user, there is a secondary vertical axis with one series collection that stands out from the other collection's values (eg: the values are percentage changes of the other values from the primary vertical axis. When I toggle my chart types, I eventually toggle from xlLineMarkers to the xlColumnClustered type. When this happens, I would like the secondary series collection type to remain a xlLineMarkers while the rest of the primary vertical axis types change to xlColumnClustered types. A part of the code I have to toggle is:

Select Case ChrtTogl
        '''''''''''''''''''''''''''''''
        '''Control the Case count   '''
        Case Is > 5
            ChrtTogl = 5
        Case 1
            Chrt.ChartType = xlLine
            If EscBool = True Then
                'For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(Scnt - 1)
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .MarkerStyle = 1
                        .MarkerSize = 5
                    End With
                    With Chrt
                        .SetElement (msoElementSecondaryValueAxisTitleHorizontal)
                        .Axes(xlValue, xlSecondary).AxisTitle.Text = "%Change"
                        .Axes(xlValue, xlSecondary).AxisTitle.Left = 254.533
                        .Axes(xlValue, xlSecondary).AxisTitle.Top = 22
                    End With
                'Next SeriesCnt
            Else
                For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(SeriesCnt)
                        .AxisGroup = 1
                        .MarkerStyle = 0
                    End With
                Next SeriesCnt
                With Chrt
                    .SetElement msoElementPrimaryCategoryAxisReverse
                    .Axes(xlValue, xlSecondary).AxisTitle.Delete
                    .SetElement msoElementPrimaryValueAxisTitleHorizontal
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Index = 100"
                    .Axes(xlValue, xlPrimary).AxisTitle.Top = 22
                End With
            End If
        Case 2
            Chrt.ChartType = xlLineMarkers
            If EscBool = True Then
                'For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(Scnt - 1)
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .MarkerStyle = 1
                        .MarkerSize = 5
                    End With
                    With Chrt
                        .SetElement (msoElementSecondaryValueAxisTitleHorizontal)
                        .Axes(xlValue, xlSecondary).AxisTitle.Text = "%Change"
                        .Axes(xlValue, xlSecondary).AxisTitle.Left = 254.533
                        .Axes(xlValue, xlSecondary).AxisTitle.Top = 22
                    End With
                'Next SeriesCnt
            Else
                For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(SeriesCnt)
                        .AxisGroup = 1
                        .MarkerStyle = 0
                    End With

                Next SeriesCnt
                With Chrt
                    .SetElement msoElementPrimaryCategoryAxisReverse
                    .Axes(xlValue, xlSecondary).AxisTitle.Delete
                    .SetElement msoElementPrimaryValueAxisTitleHorizontal
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Index = 100"
                    .Axes(xlValue, xlPrimary).AxisTitle.Top = 22
                End With
            End If
        Case 3
            Chrt.ChartType = xlColumnClustered
            If EscBool = True Then
                Debug.Print Chrt.SeriesCollection(Scnt - 1).Name
                'For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(Scnt - 1)
                    
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .MarkerStyle = 1
                        .LineType = xlLineMarkers
                        .MarkerSize = 5
                    End With
                    With Chrt
                        .SetElement (msoElementSecondaryValueAxisTitleHorizontal)
                        .Axes(xlValue, xlSecondary).AxisTitle.Text = "%Change"
                        .Axes(xlValue, xlSecondary).AxisTitle.Left = 254.533
                        .Axes(xlValue, xlSecondary).AxisTitle.Top = 22
                    End With
                'Next SeriesCnt
            Else
                For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(SeriesCnt)
                        .AxisGroup = 1
                        .MarkerStyle = 0
                    End With

                Next SeriesCnt
                With Chrt
                    .SetElement msoElementPrimaryCategoryAxisReverse
                    .Axes(xlValue, xlSecondary).AxisTitle.Delete
                    .SetElement msoElementPrimaryValueAxisTitleHorizontal
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Index = 100"
                    .Axes(xlValue, xlPrimary).AxisTitle.Top = 22
                End With
            End If
        Case 4
            Chrt.ChartType = xlCylinderColClustered
            If EscBool = True Then
                'For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(Scnt - 1)
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .MarkerStyle = 1
                        .MarkerSize = 5
                    End With
                    With Chrt
                        .SetElement (msoElementSecondaryValueAxisTitleHorizontal)
                        .Axes(xlValue, xlSecondary).AxisTitle.Text = "%Change"
                        .Axes(xlValue, xlSecondary).AxisTitle.Left = 254.533
                        .Axes(xlValue, xlSecondary).AxisTitle.Top = 22
                    End With
                'Next SeriesCnt
            Else
                For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(SeriesCnt)
                        .AxisGroup = 1
                        .MarkerStyle = 0
                    End With

                Next SeriesCnt
                With Chrt
                    .SetElement msoElementPrimaryCategoryAxisReverse
                    .Axes(xlValue, xlSecondary).AxisTitle.Delete
                    .SetElement msoElementPrimaryValueAxisTitleHorizontal
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Index = 100"
                    .Axes(xlValue, xlPrimary).AxisTitle.Top = 22
                End With
            End If
        Case 5
            Chrt.ChartType = xlConeColClustered
            If EscBool = True Then
                'For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(Scnt - 1)
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .MarkerStyle = 1
                        .MarkerSize = 5
                    End With
                    With Chrt
                        .SetElement (msoElementSecondaryValueAxisTitleHorizontal)
                        .Axes(xlValue, xlSecondary).AxisTitle.Text = "%Change"
                        .Axes(xlValue, xlSecondary).AxisTitle.Left = 254.533
                        .Axes(xlValue, xlSecondary).AxisTitle.Top = 22
                    End With
                'Next SeriesCnt
            Else
                For SeriesCnt = 1 To Chrt.SeriesCollection.Count
                    With Chrt.SeriesCollection(SeriesCnt)
                        .AxisGroup = 1
                        .MarkerStyle = 0
                    End With

                Next SeriesCnt
                With Chrt
                    .SetElement msoElementPrimaryCategoryAxisReverse
                    .Axes(xlValue, xlSecondary).AxisTitle.Delete
                    .SetElement msoElementPrimaryValueAxisTitleHorizontal
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Index = 100"
                    .Axes(xlValue, xlPrimary).AxisTitle.Top = 22
                End With
            End If
        End Select

For Case 3 I tried to change the LineType of the series collection but I receive an error for my troubles (Run Time 438 Object doesn't support this property or method). Obviously I am going about this wrong so if someone can steer me straight on this I would be most appreciative.

A series does not have a LineStyle property. You need to set the ChartType property.

                    With Chrt.SeriesCollection(Scnt - 1)
                        .Name = EscRng & " (Right Side %Change)"
                        '.numberformat=
                        .AxisGroup = 2
                        .ChartType = xlLineMarkers
                        .MarkerStyle = 1

You always have all the right answers regarding charts. You are amazing Andy Pope! Thank you so much for that. The funny thing about using .ChartType is, I didn't try because I assumed that it would change the types of all collections. At any rate, this is now solved and I have to create a new thread for a new problem.