Change scatter plot XY series to named ranges with VBA

Question

I have a dynamic scatter plot that changes with a slider button. In order to do that i use two named ranges with the offset function, these named ranges vary when i move the slider button increasing or decreasing the X and Y ranges end value. The problem arises when i make a copy of this sheet. The chart in the new sheet won't have the named range on the series formula, but instead it adopts the range that was calculated in the first sheet, like this:

In the first sheet i have this:

=Serie('old_sheet'!$AD$3;'old_sheet'!DEF_RANGE;'old_sheet'!STRESS_RANGE;1)

But when i make a copy, the chart in the new sheet will become:

=Serie('new_sheet'!$AD$3;'new_sheet'!$G$19:$G$578;'new_sheet'!$F$19:$F$578;1)

So i need to change the formula manually to this:

=Serie('new_sheet'!$AD$3;'new_sheet'!DEF_RANGE;'new_sheet'!STRESS_RANGE;1)

I ask if anyone can help me implement a simple button that changes the series formula to what i want.

I have tried to record a macro of the steps i do to change the formula... but it doesn't always works.

Kind of stuck here... i would appreciate any help!

Regards

Answer 1

I have figured it out myself... Here's the code i used:

Private Sub CommandButton1_Click()


    sheet_name = ActiveSheet.Name
    MsgBox ("some message")
    ActiveSheet.ChartObjects("Graph_1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveSheet.ChartObjects("Graph_1").Chart.SeriesCollection(1).Values = " '" & sheet_name      & "'!stress_range"
    ActiveSheet.ChartObjects("Graph_1").Chart.SeriesCollection(1).XValues = " '" & sheet_name & "'!def_range"


End Sub

Hope this can be useful for anyone with the same problem as me.

Thanks

Answer 2

The following procedure is a bit of a pain, but it preserves the fragile worksheet-scoped names.

1. Save the workbook with the worksheet and chart.
2. Move (don't copy) the sheet with the chart to a new workbook.
3. Close the first workbook without saving, so upon reopening it will still have the worksheet and chart in it.
4. Save and close the new workbook with the moved worksheet and chart (e.g. TempChart.xlsx). This workbook can now serve as a template if used as in step 6 below.
5. Reopen the original workbook.
6. Reopen the new workbook. Move (don't copy) the worksheet and chart from the new workbook to the original workbook.

Whenever you need another copy of this worksheet and chart, reopen the new workbook as in step 6 and move the sheet into the desired existing workbook.

Answer 3

I came up with a small variation on a big program I have, which assumes one chart on the copied sheet and one plotted series in that chart.

Sub FixSeriesRangeRefs()
  Const sXVALNAME As String = "DEF_RANGE"
  Const sYVALNAME As String = "STRESS_RANGE"

  Dim sFormula As String
  Dim vFormula As Variant
  Dim sXVals As String
  Dim sYVals As String

  With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    sFormula = .Formula
    vFormula = Split(sFormula, ",")

    sXVals = vFormula(LBound(vFormula) + 1)
    sXVals = Left$(sXVals, InStr(sXVals, "!")) & sXVALNAME
    vFormula(LBound(vFormula) + 1) = sXVals

    sYVals = vFormula(LBound(vFormula) + 2)
    sYVals = Left$(sYVals, InStr(sYVals, "!")) & sYVALNAME
    vFormula(LBound(vFormula) + 2) = sYVals

    sFormula = Join(vFormula, ",")
    .Formula = sFormula
  End With
End Sub