Dynamic Chart with VBA

[Pinkoto]

Hello im using this VBA code that makes my chart dynamic.

<code>Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2:L2")) Is Nothing Then
    With Me
        .ChartObjects("Chart 1").Activate
        ActiveChart.SetSourceData Source:=.Range(.Cells(1, 1), .Cells(1 + [K2].Value, 1 + [L2].Value))
    End With
End If
End Sub</code>

<code>I type it in VBA in Sheet 1 where the data source is. If the chart is there too it will work perfectly. But i want to put the chart in Sheet2. Can you help me do this?

Also K2 and L2 keep the values that show how much columns and rows should the chart take from the table. I want to keep them too in Sheet1. THe only thing in sheet2 has to be the chart.
</code>

 

[tonyyy]

Pinkoto,

Cut and paste "Chart 1" from Sheet1 to Sheet2, then make the following modification to the code...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2:L2")) Is Nothing Then
    With Me
        [COLOR=#ff0000]Sheets("Sheet2")[/COLOR].ChartObjects("Chart 1").Activate
        ActiveChart.SetSourceData Source:=.Range(.Cells(1, 1), .Cells(1 + [K2].Value, 1 + [L2].Value))
    End With
End If
End Sub

Cheers,

tonyyy

 

[Pinkoto]

Thank you. But i seem to find another problem. The values in cell K and L are formulas (count). When they are simple numbers the chart works perfectly but when they are count formula it seems to give mixed results in the chart. Is it better if the count is put in the VBA code itself? If so how can i make it ?

 

[tonyyy]

You can leave your formulas in K and L, and make the changes below...

Private Sub Worksheet_Calculate()
    With Me
        Sheets("Sheet2").ChartObjects("Chart 1").Activate
        ActiveChart.SetSourceData Source:=.Range(.Cells(1, 1), .Cells(1 + [K2].Value, 1 + [L2].Value))
    End With
End Sub

 

[Pinkoto]

THANK YOU!!! I have worked on this project for some time now and with your help i think i have finished it . I only hope i dont see the need for more debugging.

 

[tonyyy]

You're very welcome...

 

[Jon Peltier]

Actually, using square brackets to reference cells is not as explicit as referencing them by sheet. And I would expect an error if you try to activate a chart not on the active sheet. And you don't need to activate a chart or even have it on the active sheet to modify it in VBA.

So here's what I would use:

Private Sub Worksheet_Calculate()
    With Me
        Sheets("Sheet2").ChartObjects("Chart 1").Chart.SetSourceData Source:=Range(.Cells(1, 1), .Cells(1 + .Range("K2").Value, 1 + .Range("L2").Value))
    End With
End Sub

 

[tonyyy]

Thanks for stepping in, Jon.

 

[Pinkoto]

thank you!

But everytime i add something to any sheet of the file it automatically sends me to the sheet with the chart like its refreshing it. How can i stop that?

 

[Jon Peltier]

See Tony's code, which activates the chart, then does something to the activechart?

Don't activate the chart. Use my version, which references the chart without activating it, and makes the change.