Change Datasource of a chartsheet (no embedded sheet)

Question

I have a dataset with is quite simple. When I add data at the end of it, I want to automatically get that in my chartsheet.

For example:

At this moment, the chartsheet exists with a Datasource "A1:Q10". After I enter some values within the sheet where the Datascource is, it should change to A1:R10.

But it doens't work. Here the code which ends up in

"Application or object-defined error"

Sheets("_Chartsheet-sheet").Select
Activesheet.Chartobjects(1).Activate
ActiveChart.SetSourceDate Source:=Range("A10:" & ActualLetter & "10")

Do you need to prefix a10:xyz10 with a sheet name like `sheet1!a10:z10`? — Nathan_Sav, Mar 19 '18 at 15:27
i did it as well with "Source: Worksheet("_xyz_").Range("A1......") - didnt work neither — Jan Klar, Mar 19 '18 at 15:34
For starters, `SetSourceDate` isn't a valid method. Did you mean: `SetSourceData` ? — ashleedawg, Mar 19 '18 at 15:34
These kinds of mistakes can be avoided by always putting this as the first line in every module: **`Option Explicit`** This forces you to properly declare and refer to objects, variable, properties, etc. — ashleedawg, Mar 19 '18 at 15:42

Mathieu Guindon · Accepted Answer · 2018-03-19T15:54:38.007

As ashleedawg noted, SetSourceDate isn't a member of the Chart class - using Option Explicit and the VBE's IntelliSense & auto-complete will help prevent these. Writing it off as a typo, there's another problem with the code...

Source:=Range("A10:" & ActualLetter & "10")

Range being unqualified, if that code is written in a standard code module, it implicitly refers to whatever the ActiveSheet is - in this case...

Sheets("_Chartsheet-sheet").Select

...a chart sheet, which isn't a Worksheet and, as such, doesn't have a Range property.

An unqualified Range call, made explicit, reads as follows:

[_Global].Range("address")

The solution is to properly qualify that Range call with the Worksheet object instance you mean to work with - assuming Sheet1:

Source:=Sheet1.Range("address")

That Sheet1 is a global-scope Worksheet object that you get for free, given that your worksheet exists at compile-time. Select the worksheet under "Microsoft Excel Objects" in the VBE's Project Explorer, then look at its properties (F4): you'll notice it has a Name property with a value that corresponds to its "tab name" (the name you'd use to fetch the worksheet by name), but it also has a (Name) property, with a value that corresponds to its "code name" - that's the identifier VBA uses to generate the free global-scope object variable you can (and should) use anywhere in your code whenever you need to refer to that worksheet.

Fetching that object from the Sheets or Worksheets collection by name, puts your code at risk: unless workbook structure is protected, the user can change that "tab name" at any time, and that will break every Worksheets("name") call in your code.

If the code is written in a worksheet's code-behind, then the unqualified Range call is implicitly referring to that worksheet. In that case, explicitly qualifying Range with Me would make the code more... explicit about its intent.

Lastly, .Select and .Activate aren't needed here. Avoid Select and Activate, it's macro-recorder code that's as frail as code gets. Instead, work the object references - here I'm assuming that the code is written in the source sheet's code-behind, hence the Me qualifier in front of Range:

Dim chartSheet As Chart
Set chartSheet = ThisWorkbook.Sheets("_Chartsheet-sheet")
chartSheet.SetSourceData Source:=Me.Range("A10:" & ActualLetter & "10")

Sorry for taking so long. Had a little incident. It worked! Thank you so much. Really lovely explained and on a very good level. — Jan Klar, Mar 27 '18 at 18:28

Change Datasource of a chartsheet (no embedded sheet)

1 Answers1