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>

 

[Pinkoto]

thank you!

 

[Pinkoto]

can you help me format the chart bars with VBA? Im trying to make a dark outline to the bars. If i do it with the normal settings the outline wont appear again if i have removed and then added some of the bars

 

[Jon Peltier]

Record a macro, then apply the formatting that you want. What code is recorded? Post it along with your Worksheet_Calculate procedure, and I'll combine them for you.

 

[Pinkoto]

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

   ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    ActiveChart.ChartArea.Select
End Sub

This is only for one bar chart. Im trying to make it for every bar chart that is active at a time which is a dynamic value. When done this code wont make the same problem like before (going to the chart when you add a value to any cell in the workbook)?

 

[Jon Peltier]

Private Sub Worksheet_Calculate()
    Dim cht As Chart
    Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    With Me
        cht.SetSourceData Source:=Range(.Cells(31, 2), .Cells(31 + .Range("K2").Value, 2 + .Range("L2").Value))
    End With
    With cht.FullSeriesCollection(1).Format.Line
        .Visible = msoTrue '' see note
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0 '' see note
        .ForeColor.Brightness = 0 '' see note
        .Transparency = 0 '' see note
        '' Note: these are probably not needed
    End With
End Sub

This code does not activate anything, and it does not rely on editing the active chart.

 

[Pinkoto]

This applies for only of the legend entries. How can i make it work for all of them no matter how many are there?

 

[Jon Peltier]

To be precise, it applies to one of the series in the chart. If the series is shown in the legend, then the corresponding legend entry shows the formatting.

Do you want all series to look the same? Then it's easy:

Private Sub Worksheet_Calculate()
    Dim cht As Chart, srs As Series
    Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    With Me
        cht.SetSourceData Source:=Range(.Cells(31, 2), .Cells(31 + .Range("K2").Value, 2 + .Range("L2").Value))
    End With
    For Each srs in cht.FullSeriesCollection
        With srs.Format.Line
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        End With
    Next
End Sub

If the series are not intended to all have the same formatting, then you need to loop more carefully through the series collection, applying specific formatting to each series based on its name or plot order (or some other parameter).