Update multiple chart series & multiple chart with VBA

M

mts91

New Member
Joined
Jul 4, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hope you are doing well.

I'm coming on this forum because i really need your help and i'm a big beginner with VBA.

I have 3 charts :

-Chart1
-Chart2
-Chart3

I want to update multiple series ranges on these charts with a one VBA code like below :

Chart 1
-Update "Serie 1" with the range B2:B14
-Update "Serie 2" with the range C4:C12
-Update "Serie 3" with the range D5:C10

Chart 2
-Update "Serie 1" with the range E4:E10
-Update "Serie 2" with the range G3:E10

Series names and Chart names are like above, and the chart and series are already build with all.
I just want to update the series range on these chart, i don't want to modify the series name or the layout of the series.

Do you think someone can help me with this ? As i said i'm starting to use VBA and i'm really blocked with this operation.

Thanks a lot in advance.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
A general example:

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub
 
Upvote 0
Thanks a lot for your code and your answer.

Could you just please explain me what the code actually do ? Apologies i'm a big beginner ahah.

I'm asking that because i don't see where you are updating the second chart on this code, so i don't know if your code is updating the second chart also or if you just gave me the code structure.
 
Upvote 0
mts91 said:
Thanks a lot for your code and your answer.

Could you just please explain me what the code actually do ? Apologies i'm a big beginner ahah.

I'm asking that because i don't see where you are updating the second chart on this code, so i don't know if your code is updating the second chart also or if you just gave me the code structure.
Click to expand...

Sorry that was a typo error on my part. The example was for Chart 1 using the data you posted:

mts91 said:
Chart 1
-Update "Serie 1" with the range B2:B14
-Update "Serie 2" with the range C4:C12
-Update "Serie 3" with the range D5:C10
Click to expand...

But my post referenced "Chart 2". Corrected version:

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 1").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub

That said, it is a general example, not a specific example. You did not provide enough information for that. So the code example is for is an embedded chart object named "Chart 1" located on Sheet1 and plotting data from Sheet1.
 
Upvote 0
Your code is perfect, it's working well on my file because you have guessed where the chart & data are located. So i totally understood that your first code was for the chart called "Chart 2"

But my second question was more to ask if it was possible to update both charts with the series described above in the same code. Like combining your two codes into one, so when I will run the macro both graphs will be updated

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub

&

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        
        CH.SeriesCollection(1).Values = WS.Range("E4:E10")
        CH.SeriesCollection(2).Values = WS.Range("G4:E10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub
 
Upvote 0
mts91 said:
Your code is perfect, it's working well on my file because you have guessed where the chart & data are located. So i totally understood that your first code was for the chart called "Chart 2"

But my second question was more to ask if it was possible to update both charts with the series described above in the same code. Like combining your two codes into one, so when I will run the macro both graphs will be updated

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
   
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
   
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub

&

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
   
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
   
    If Not CH Is Nothing Then
       
        CH.SeriesCollection(1).Values = WS.Range("E4:E10")
        CH.SeriesCollection(2).Values = WS.Range("G4:E10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub
Click to expand...
I'm not really clear apologies ahah, basically my question is : How can i combine these two codes into one ?
 
Upvote 0
Just put them into the same procedure
VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 1").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject(Chart 1)"
    End If

' next chart
    Set CH = Nothing
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        
        CH.SeriesCollection(1).Values = WS.Range("E4:E10")
        CH.SeriesCollection(2).Values = WS.Range("G4:E10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
    Else
        MsgBox "No such ChartObject(Chart 2)"
    End If
End Sub
 
Upvote 0
rlv01 said:
Just put them into the same procedure
VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Sheet1")
   
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 1").Chart
    On Error GoTo 0
   
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("B2:B14")
        CH.SeriesCollection(2).Values = WS.Range("C4:C12")
        CH.SeriesCollection(3).Values = WS.Range("D5:D10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
    Else
        MsgBox "No such ChartObject(Chart 1)"
    End If

' next chart
    Set CH = Nothing
    On Error Resume Next
    Set CH = WS.ChartObjects("Chart 2").Chart
    On Error GoTo 0
   
    If Not CH Is Nothing Then
       
        CH.SeriesCollection(1).Values = WS.Range("E4:E10")
        CH.SeriesCollection(2).Values = WS.Range("G4:E10")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
    Else
        MsgBox "No such ChartObject(Chart 2)"
    End If
End Sub
Click to expand...
Ok thank you for your answer and for your code. This code works also if i want to add a third chart ? like can i just copy/past from "next chart" and edit with my chart settings ?
 
Upvote 0
Actually i have the below error when i'm running your code

1657032410914.png


I used the below code, and i think it's the same as you just with more parameters

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Overview")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Connexion_Graph").Chart
    On Error GoTo 0
    
    If Not CH Is Nothing Then
        CH.SeriesCollection(1).Values = WS.Range("E94:E117")
        CH.SeriesCollection(2).Values = WS.Range("K94:K117")
        CH.SeriesCollection(3).Values = WS.Range("M94:M117")
        CH.SeriesCollection(4).Values = WS.Range("$R5:$R16,$R18:$R29")

        Debug.Print CH.SeriesCollection(1).Formula
        Debug.Print CH.SeriesCollection(2).Formula
        Debug.Print CH.SeriesCollection(3).Formula
        Debug.Print CH.SeriesCollection(4).Formula
    Else
        MsgBox "No such ChartObject"
    End If
End Sub

The error come from this line

VBA Code: 
CH.SeriesCollection(1).Values = WS.Range("E94:E117")
 
Upvote 0
It probably means that series does not exist for ChartObjects("Connexion_Graph")

See where the error occurs with this, along with how many series are in the chart.

VBA Code: 
Sub ChExample()
    Dim WS As Worksheet
    Dim CH As Chart

    Set WS = Worksheets("Overview")
    
    On Error Resume Next
    Set CH = WS.ChartObjects("Connexion_Graph").Chart
    On Error GoTo 0
    
    Debug.Print "Series count = " & CH.SeriesCollection.Count
    
    If Not CH Is Nothing Then
        Debug.Print CH.SeriesCollection(1).Formula
        CH.SeriesCollection(1).Values = WS.Range("E94:E117")
        
        Debug.Print CH.SeriesCollection(2).Formula
        CH.SeriesCollection(2).Values = WS.Range("K94:K117")
        
        Debug.Print CH.SeriesCollection(3).Formula
        CH.SeriesCollection(3).Values = WS.Range("M94:M117")
        
        Debug.Print CH.SeriesCollection(4).Formula
        CH.SeriesCollection(4).Values = WS.Range("$R5:$R16,$R18:$R29")
    Else
        MsgBox "No such ChartObject"
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
402
OrangeJuice
O
G
  • Question
bubble chart with multiple series
Replies
0
Views
672
Glainer
G
D
  • Solved
How does one access a chart Series address in VBA?
Replies
8
Views
607
DrJBN
D
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
255
Nick Loughton
N
B
  • Solved
I cannot seem to rename charts in Excel
Replies
7
Views
227
bobsan42
bobsan42

Forum statistics

Threads
1,215,528
Messages
6,125,338
Members
449,218
Latest member
Excel Master

Share this page

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