Excel Chart range based on the value in a cell in another sheet

Question

I have a sheet (Dashboard) that has multiple Pareto charts, another sheet (Data) brings in the range for each chart via a formula in standard $A$1:$B$2 format.

how do I use these ranges from the Sheet "Data" in the Pareto charts in the "Dashboard"? Chart name is in Data B4 Chart Range is in Data C4 I have code for each chart for troubleshooting below is one from a single chart

Sub FirstChart()
    Dim FirstChartName As String
    Dim FirstChartRange As Range
    
        FirstChartName = Sheets("Data").Range("B4")
        Set FirstChartRange = Worksheets("Data").Range(Sheets("Data").Range("C4").Value)
        Sheets("Dashboard").ChartObjects("FirstChart").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = FirstChartName 
        ActiveChart.SetSourceData Source:=FirstChartRange
End Sub

Thanks in advance.

UPDATE: Thanks to @coross24 and @WIL. i have uploaded the file based on their answers to https://gofile.io/d/8HfjQv

score 3 · Answer 1 · answered Sep 02 '20 at 20:51

It seems you were slightly off when referencing the FirstChartRange parameter. Since the variable was bound as a Range, what you've done is reference the cell C4 as the range, rather than the string within that range, in turn trying to plot the string value within that cell! When running your code, I ran into a type error.
I've amended your code above and tested it out on a singe chart in my workbook and it seems to work okay. I've also early bound your worksheets so you don't have to repeat yourself in your code.

Sub FirstChart()
    Dim FirstChartName As String
    Dim FirstChartRange As String
    Dim shtData As Excel.Worksheet
    Dim shtDashboard As Excel.Worksheet
    Dim chart As Excel.chart
    
    Set shtData = ThisWorkbook.Sheets("Data")
    Set shtDashboard = ThisWorkbook.Sheets("Dashboard")
    
    ' get chart name
    FirstChartName = shtData.Range("B4").Value2
    ' get chart range
    FirstChartRange = shtData.Range("C4").Value2
    ' change data for first chart
    Set chart = shtDashboard.ChartObjects("FirstChart").chart
    With chart
        .HasTitle = True
        .ChartTitle.Text = FirstChartName
        .SetSourceData shtData.Range(FirstChartRange)
    End With
       
End Sub

Good luck!

i tried you code as is, but im getting an error "Run-time error'445': Object doesn't support this action". i used the line by line debug and the issue is in the line .SetSourceData shtData.Range(FirstChartRange). any ideas why?. — rellik, Sep 05 '20 at 03:25

score 3 · Answer 2 · answered Sep 05 '20 at 04:03

3

Try this One

Sub FirstChart()

Dim FirstChartName As String
Dim FirstChartRange As String
Dim shtData As Excel.Worksheet
Dim shtDashboard As Excel.Worksheet
Dim chart As Excel.chart

Set shtData = ThisWorkbook.Sheets("Data")
Set shtDashboard = ThisWorkbook.Sheets("Dashboard")

' get chart name
FirstChartName = shtData.Range("B4").Value2
' get chart range
FirstChartRange = shtData.Range("C4").Value2
' change data for first chart
Set chart = shtDashboard.ChartObjects("FirstChart").chart
With chart
    .HasTitle = True
    .ChartTitle.Text = FirstChartName
    .SetSourceData FirstChartRange
End With
   
End Sub

answered Sep 05 '20 at 04:03

WIL

117
9

hi thanks for the answer, however its still not working. i figured my formula in sheet "Data" C4 that return the range was only returning as $A$50:$B$67 with out the sheet identifier it may have be the reason. since i change the formula to return the value as Data!$A$50:$B$67 but still no luck now i have error "Type Mismatch" if i revert back to the formula without the sheet name i still get object does not support this action" error thanks in advance – rellik Sep 07 '20 at 23:35
i have uploaded my file to https://gofile.io/d/8HfjQv – rellik Sep 08 '20 at 01:16
To accept an answer: Choose one answer that you believe is the best solution to your problem. To mark an answer as accepted, click on the check mark beside the answer to toggle it from greyed out to filled in. You may change which answer is accepted, or simply un-accept the answer, at any time. – WIL Sep 09 '20 at 05:41

score 2 · Accepted Answer · answered Sep 08 '20 at 18:37

Relik,
I've had to post another answer as my reputation isn't high enough to reply with a comment. There's an absolutely filthy work around.... it seems the data does actually populate the graph is you just bypass the error message, and then set the y-axis scale to auto. See below for the code:

Option Explicit

Sub FirstChart()
    Dim FirstChartName As String
    Dim FirstChartRange As String
    Dim rng As Range
    Dim r As Range
    Dim shtData As Excel.Worksheet
    Dim shtDashboard As Excel.Worksheet
    Dim chart As Excel.chart
    Dim tmp As Variant

    Set shtData = ThisWorkbook.Sheets("Data")
    Set shtDashboard = ThisWorkbook.Sheets("Dashboard")
    
    ' get chart name
    FirstChartName = shtData.Range("B4").Value2
    ' get chart range
    FirstChartRange = shtData.Range("C4").Value2
    
    ' change data for first chart
    Set chart = shtDashboard.ChartObjects("FirstChart").chart
    With chart
        .HasTitle = True
        .ChartTitle.Text = FirstChartName
        On Error Resume Next
        .SetSourceData shtData.Range(FirstChartRange)
        On Error GoTo 0
        .Axes(xlValue).MaximumScaleIsAuto = True
    End With
       
End Sub

Hope this helps with your issue!

Wow great work @coross24, i had this issue in a previous version of the charts, i thought i could get it to work without skipping over the error. for some reason my code worked without the "On Error Resume Next" in windows 7 since upgrading to windows 10 it required it. Thanks for your help, enjoy the Bounty — rellik, Sep 10 '20 at 03:40

sancho.s ReinstateMonicaCellio · Answer 4 · 2020-09-06T09:11:45.217

This is an example for creating a scatter plot. It should get you going. Adapt it to your needs.

Sub CreateChart()
    Dim wscharts As Worksheet, wsdata As Worksheet
    Set wscharts = Worksheets("Dashboard")
    Set wsdata = Worksheets("Data")
    Dim sh As Shape
    Set sh = wscharts.Shapes.AddChart2(240, xlXYScatterLines)
    sh.Select
    Dim rngText As String
    rngText = wsdata.Name & "!" & wsdata.Range("Data!$C$4").Value
    ActiveChart.SetSourceData Source:=Range(rngText)
    sh.Name = wsdata.Range("Data!$B$4").Value
End Sub

It works fine with the data as shown

Excel Chart range based on the value in a cell in another sheet

4 Answers4

Linked