Charts won't maintain series color when only changing values with VBA

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
349
Office Version
2016
Platform
Windows
When I attempt to use the following to update series values using an array, The series don't maintain their coloration and defaults to one of the available presets.
Code:
With Chart_Dictionary.Item("Non-C").Chart

    For L = 1 To 4

        .FullSeriesCollection(L).XValues = Date_Range

        Select Case L

            Case 1: .FullSeriesCollection("1").Values = This_Column(AR, 4) 
            Case 2: .FullSeriesCollection("2").Values = This_Column(AR, 5) 
            Case 3: .FullSeriesCollection("3").Values = This_Column(AR, 3) 
            Case 4: .FullSeriesCollection("4").Values = This_Column(AR, 6) 
            
        End Select

    Next L

End With
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
349
Office Version
2016
Platform
Windows
Another problem is that it is unhiding hidden series

Full macro but using a range instead of an array.

Code:
Private Sub Update_Charts() 'changing Chart Data

Dim TT As Long, AR As Range, Date_Range As Range, CN As Byte, DD As Byte, Item As ChartObject, Enter_Function As Boolean

CN = Variable_Sheet.ListObjects("Saved_Variables").DataBodyRange.Cells(2, 2).Value2

Application.ScreenUpdating = False

Set AR = ThisWorkbook.Worksheets(Chart_Sheet.Sheet_Selection.Value).range("A1").ListObject.DataBodyRange '.SpecialCells(xlCellTypeVisible)

'Chart_Sheet.Sheet_Selection IS A COMBOBOX


Set Date_Range = AR.Columns(1)

For Each Item In Chart_Sheet.ChartObjects
    
    With Item.Chart
        
        Select Case Item.Name
        
            Case "Net"
    
                For TT = 1 To 4
            
                    .FullSeriesCollection(TT).XValues = Date_Range
                    
                    Select Case TT
                                                                            
                        Case 1: .FullSeriesCollection("Commercial").Values = AR.Columns(CN)            'Commercial 
                        Case 2: .FullSeriesCollection("Non-Commercial").Values = AR.Columns(CN + 1)  'Non-Commercial
                        Case 3: .FullSeriesCollection("Non-Reportable").Values = AR.Columns(CN + 2)  'Non-Reportable 
                        Case 4: .FullSeriesCollection("OI").Values = AR.Columns(3)     'Open Interest
            
                    End Select
            
                Next TT
                
            Case "Commercial "
            
                For TT = 1 To 3
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("Commercial Long").Values = AR.Columns(7)  'Commercial L
                        Case 2: .FullSeriesCollection("Commercial Short").Values = AR.Columns(8)  'Commercial S
                        Case 3: .FullSeriesCollection("OI").Values = AR.Columns(3)  'OI
            
                    End Select
            
                Next TT
            
            Case "Non-Commercial Positions"
                    
                For TT = 1 To 4
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("Non-Commercial Long").Values = AR.Columns(4)  'N-Commercial L
                        Case 2: .FullSeriesCollection("Non-Commercial Short").Values = AR.Columns(5)  'N-Commercial S
                        Case 3: .FullSeriesCollection("OI").Values = AR.Columns(3)  'OI
                        Case 4: .FullSeriesCollection("Non-Commercial S").Values = AR.Columns(6)  'N-Spread
                        
                    End Select
            
                Next TT
                
            Case "Commercial % "
            
                For TT = 1 To 2
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection(OI-Long (All)").Values = AR.Columns(27)  
                        Case 2: .FullSeriesCollection("OI-Short (All)").Values = AR.Columns(28) 
            
                    End Select
            
                Next TT
            
            Case "Non-Commercial % OI"
            
                For TT = 1 To 2
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("% of OI-Noncommercial-Long (All)").Values = AR.Columns(24)  'N-Commercial L OI%
                        Case 2: .FullSeriesCollection("% of OI-Noncommercial-Short (All)").Values = AR.Columns(25)  'N-Commercial S OI%
            
                    End Select
            
                Next TT
        
            Case "MoI"
    
                DD = CN + 12: Enter_Function = True
        
            Case "6M"
    
                DD = CN + 10: Enter_Function = True
                
            Case "3Y"
                
                DD = CN + 11: Enter_Function = True
                
        End Select
        
        If Enter_Function = True Then Call Just_One_Series(Date_Range, Item, DD, AR)
        
        Enter_Function = False
  
    End With

Next Item

Application.ScreenUpdating = True

End Sub
Code:
Private Sub Just_One_Series(Date_Range As Range, This_Chart As Variant, ColumnN As Byte, ART As Range)

With This_Chart.Chart

    .FullSeriesCollection(1).XValues = Date_Range
    .FullSeriesCollection(1).Values = ART.Columns(ColumnN)
    
End With

End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,269
Messages
5,467,661
Members
406,546
Latest member
JennieK

This Week's Hot Topics

Top