Results 1 to 2 of 2

Thread: Charts won't maintain series color when only changing values with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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 by MoshiM; Jul 23rd, 2019 at 11:42 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •