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

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
429
Office Version
  1. 2016
Platform
  1. 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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top