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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

B
  • Question Question
Retain Chart Formatting when change series references
Replies
2
Views
241
Jon Peltier
J
M
  • Solved
VBA Chart Series title with multiple cells
Replies
4
Views
87
mikejvir
M
M
  • Question Question
Changing of Chart's Column Data Series Conveniently
Replies
2
Views
123
mangocat123
M
M
  • Question Question
Is it possible to dynamically add chart series' based on dynamic named range?
Replies
1
Views
665
StephenCrump
S
T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
171
TheTiredEngineer
T

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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