0

I have an excel workbook (v2016), where I managed to get the table name of a table based on the highest number of entries. This value (which is a name of a table) is in Sheet "Pareto" Cell B4.

I'm using the following code to update a chart using an above-mentioned table name

Sub GetTables()

Dim YTDL1TableName As String
Dim YTDL1Range As Range

YTDL1TableName = Sheets("Pareto").Range("B4")
Set YTDL1Range = Sheets("Pareto").Range(Sheets("Pareto").ListObjects(YTDL1TableName).Range.Address(True, True))
Sheets("Pareto").ChartObjects("YTDL1").Activate
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = YTDL1TableName
ActiveChart.SetSourceData Source:=YTDL1Range
End Sub

While the charts do get updated I do get an error

Runtime Error '445' : Object doesn't suuport this action

How do I get rid of this error?

Thanks in advance

rellik
  • 304
  • 1
  • 4
  • 16
  • You might want to avoid the use of active chart, you should read up on https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Sep 04 '18 at 06:37

1 Answers1

0

As I understand it, you want to change the data source of the chart based on the values in the cell.

Please try the code below:

Sub GetTables()

Dim YTDL1TableName As String
Dim YTDL1Range As Range


YTDL1TableName = Sheets("Pareto").Range("B4")
'Set YTDL1Range = Sheets("Pareto").Range(Sheets("Pareto").Cells(YTDL1TableName).Address(True, True))
Worksheets("Pareto").ChartObjects("YTDL1").Activate
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = YTDL1TableName
ActiveChart.SetSourceData Source:=Sheets("Pareto").Range("B4")

End Sub

Here is the result： Here is the result

Hope that helps!

Community
  • 1
  • 1
Lina
  • 261
  • 1
  • 4
  • Thank you @Lina for your reply, cell B4 returns a table name. YTDL1Range is to get the range address. I have updated the code to reflect this. The table name behind B4 (e.g. 2018Sales which is a table name, not an actual data value) changes dynamically. I want the graphs to change with whatever table name B4 represent. currently, my code does update the chart but end with the above-mentioned error. – rellik Sep 04 '18 at 21:27