Code to Select source data for charts when cell value change

[kelly mort]

Row 1 is header

Like m , n, o, p, q


Then A2:A6 has the labels

A, B, C, D, E

which means that the table is A1:F6

So inside cell G3, I will place either m, n, o, p, q.

So when I place m into cell G3 then I want the source data for the chart be col A and B.

If n then select Col A and C in that other.

One thing is I will first draw the chart manually before using the code.

But better still if the chart could be deleted and redrawn each time I will appreciate that.

Thanks

 

[kelly mort]

I came across this code during my search. It seems close to my want however I am having issues tweaking it.

I want to place it under the sheet change event(from my understanding).

So that when that cell G3 from the original post is changed, then we look at the cell content of G3 and use it to determine which ranges to set for our source data.

<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace; vertical-align: baseline; max-height: 300px; overflow: auto;">Dim chart As ChartObject

ForEach chart In ActiveSheet.ChartObjects

    Dim ser As Series

    ForEach ser In chart.Chart.SeriesCollection
        Dim oF AsString
        Dim nF AsString
        oF = ser.Formula
        nF = Replace(oF,"$1","$8")' Changing the row 1 to row 8
        nF = Replace(nF,"$5","$12")' Changing the row 5 to row 12
        ser.Formula = nF
    Next ser

Next chart</code>

 

[kelly mort]

Here is yet another sample of the code that is just a step to my want.

However instead of selecting all used columns to the right, I want to select only selected columns.

Example col C and col D or col C and Col E etc.

When I am able to do this, then I will use a combobox to do the switches instead of the G3 as proposed in my previous posts .


So I am looking for how to tweak this code

Sub Test()
    Dim CHARTDATA As Range
    Set CHARTDATA = Range(Range("C22"), Range("C22").Offset(1, 0).End(xlToRight))
    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=CHARTDATA
End Sub

 

[kelly mort]

Finally I fixed it:

Private Sub CmbQuestions_Change()
    With Sheets("Sheet1")
        Dim rng1 As Range, rng2 As Range
        Set rng1 = .Range(.Cells(5, 14), .Cells(9, 14))
        Set rng2 = .Range(.Cells(5, CmbQuestions.ListIndex + 15), .Cells(9, CmbQuestions.ListIndex + 15))
        .ChartObjects("Chart 1").chart.SetSourceData Source:=Union(rng1, rng2)
    End With
End Sub