Change Datasource of a chartsheet (no embedded sheet)

Jan Klar

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")
Mathieu Guindon

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")

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Kendo - Change DataSource onclick

ComboBox DataSource Dynamically Change

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured

Change Bokeh Datasource on input change

'url' attribute is not specified and no embedded datasource could be configured

Manually change data items in datasource

Change a gridview's datasource dynamically

Change all formulas in a sheet

Work Sheet Change

Macro bugs on sheet change

How to use a embedded font in Qt Style Sheet?

Remove Google Border From Embedded Google Sheet

Embedded Google Sheet Deletes Elements After It

Scrape Embedded Google Sheet from HTML in Python

Published Google Sheet not showing in embedded iframe

Change the tabs in a Embedded TabBarController

Change CSS of an embedded widget

Python XlsxWriter ChartSheet watermark does not work

Missing methods using org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder

How to shutdown embedded Derby which is defined as a DataSource properly on WildFly

PowerBI Embedded: Datasource has no credentials, unable to Patch the gateway

SpringBoot2 Failed to auto-configure a DataSource: 'spring.datasource.url' is not specified and no embedded datasource could be auto-configured

Spring Boot: Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured. SPRING

Config-Server Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured

Gradle project --Failed to configure a DataSource'url' attribute is not specified and no embedded datasource could be configured

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.Error In Gradle project

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured. Reason: Fa

Spring Boot jdbc issue. Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured