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

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
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,102,225
Messages
5,485,506
Members
407,503
Latest member
Hereheis

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top