Change chart data source worksheet by using a drop-down cell with list of worksheets

Question

Hi, I am trying to adjust a macro that I have recorded which constructs a chart based on data in two different worksheets in the same workbook.

I have a 'comparison worksheet' with two drop-down cells that have a list of all the worksheets in the workbook. What I want the macro to do is create a chart based on the data in the two selected worksheets whose names are selected in the two drop-downs.

Any help on doing this would be much appreciated - I am unsure how to make the two cells a 'target' for 'Worksheet1' and 'Worksheet2'

My macro is currently this:

    Charts.Add

    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

    ActiveChart.SetSourceData Source:=Sheets("Comparison_sheet").Range("C3")

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).XValues = "='Worksheet1'!R11C2:R510C2"

    ActiveChart.SeriesCollection(1).Values = "='Worksheet1'!R11C9:R510C9"

    ActiveChart.SeriesCollection(1).Name = "=Comparison_sheet!R3C1"

    ActiveChart.SeriesCollection(2).XValues = "='Worksheet2'!R11C2:R510C2"

    ActiveChart.SeriesCollection(2).Values = "='Worksheet2'!R11C9:R510C9"

    ActiveChart.SeriesCollection(2).Name = "=Comparison_sheet!R5C1"

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Comparison_sheet"

    With ActiveChart

        .HasTitle = True

        .ChartTitle.Characters.Text = "Drag Development Comparison"

        .Axes(xlCategory, xlPrimary).HasTitle = True

        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X dist (dimless)"

        .Axes(xlValue, xlPrimary).HasTitle = True

        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Fx"

    End With

    ActiveSheet.Shapes("Chart 6").IncrementLeft -171.75

    ActiveSheet.Shapes("Chart 6").IncrementTop -171.75

    ActiveChart.Axes(xlCategory).Select

    With ActiveChart.Axes(xlCategory)

        .MinimumScaleIsAuto = True

        .MaximumScale = 1

        .MinorUnitIsAuto = True

        .MajorUnitIsAuto = True

        .Crosses = xlAutomatic

        .ReversePlotOrder = False

        .ScaleType = xlLinear

        .DisplayUnit = xlNone

    End With

    ActiveChart.PlotArea.Select

    With Selection.Border

        .ColorIndex = 16

        .Weight = xlThin

        .LineStyle = xlContinuous

    End With

    Selection.Interior.ColorIndex = xlNone

    ActiveChart.SeriesCollection(1).Select

    With Selection.Border

        .ColorIndex = 57

        .Weight = xlMedium

        .LineStyle = xlContinuous

    End With

    With Selection

        .MarkerBackgroundColorIndex = xlNone

        .MarkerForegroundColorIndex = xlNone

        .MarkerStyle = xlNone

        .Smooth = True

        .MarkerSize = 3

        .Shadow = False

    End With

    ActiveChart.SeriesCollection(2).Select

    With Selection.Border

        .ColorIndex = 57

        .Weight = xlMedium

        .LineStyle = xlContinuous

    End With

    With Selection

        .MarkerBackgroundColorIndex = xlNone

        .MarkerForegroundColorIndex = xlNone

        .MarkerStyle = xlNone

        .Smooth = True

        .MarkerSize = 3

        .Shadow = False

    End With

    ActiveWindow.Visible = False

    Windows("Drag Development.xls").Activate

    Range("H36").Select

End Sub

thanks in advance

John

Answer 1

Sorry - bad edit by me.

Remove the ActiveChart from each of the 3 lines it appears on.

Sub ChangeSource()

  With ActiveSheet.ChartObjects(1).Chart

    .SeriesCollection(1).XValues = "='" & Range("D3") & "'!R11C2:R510C2"

    .SeriesCollection(1).Values = "='" & Range("D3") & "'!R11C9:R510C9"

    .SeriesCollection(2).XValues = "='" & Range("E3") & "'!R11C2:R510C2"

    .SeriesCollection(2).Values = "='" & Range("E3") & "'!R11C9:R510C9"

  End With

End Sub

If that gives an error, it would be helpful to know what the error number and message is, and what the values in the relevant cells are.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

brilliant thanks very much!

Answer 3

bump

If anyone has a suggestion would be much appreciated...

many thanks

John

Answer 4

thanks very much for your reply - i tried it but there is an error - the debugger highlights line 3

thanks in advance....

John

Answer 5

I would imagine you can re-use the chart that you created with the macro rather than remaking the chart each time.

If cell D3 contains the "Worksheet1" name and E3 contains the "Worksheet2" name

Then I would think the following should work (untested)

Sub ChangeSource()

  With ActiveSheet.ChartObjects(1).Chart

    .SeriesCollection(1).XValues = "='" & Range("D3") & "'!R11C2:R510C2"

    ActiveChart.SeriesCollection(1).Values = "='" & Range("D3") & "'!R11C9:R510C9"

    ActiveChart.SeriesCollection(2).XValues = "='" & Range("E3") & "'!R11C2:R510C2"

    ActiveChart.SeriesCollection(2).Values = "='" & Range("E3") & "'!R11C9:R510C9"

  End With

End Sub

You could cause the ChangeSource macro to run automatically after D3 or E3 is changed by using a Worksheet_Change event procedure in the module of the Comparison_Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Me.Range("D3:E3")) Is Nothing Then ChangeSource

End Sub

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk