Charts.Add fails with Workbook_Open


G

Guest

I created a workbook (data.xls) with a single spreadsheet that has a
hyperlink "Create Chart" to another workbook (dataTemplate.xls).

The dataTemplate.xls workbook has the following data on sheet1 and the
following vba macros.

A B C
1 1st 2nd 3rd
2 123 456 789
3 789 456 123
4 321 654 987
5 987 654 321

'This is in the ThisWorkbook of the project
Private Sub Workbook_Open()
AddChartObject
End Sub

'This is in the Modules (Module1)
Sub AddChartObject()
Dim Cht As Excel.Chart
Set Cht = Charts.Add
With Cht
.Name = "NewChart"
.SetSourceData Source:=Sheets("Sheet1").Range("A1:C5"), _
PlotBy:=xlRows
.ChartType = xlColumnClustered
End With
End Sub

I open only the data.xls file and click on the "Create Chart" hyperlink.
This opens the dataTemplate.xls file. The Workbook_Open macro starts
automatically when the dataTemplate.xls file is opened. However, the chart
is not generated. In fact, when you click any cell on sheet1 of
dataTemplate.xls, a runtime error occurs, "Microsoft Excel has encountered a
problem and needs to close. We are sorry for the inconvenience.".

If however, I open the dataTemplate.xls file directly (not through the
hyperlink in data.xls), the chart is created and no errors.

The failure seems to occur when executing Charts.Add.

Can someone tell me what I am doing wrong? I really need this to work from
the hyperlink in the data.xls file.
 
Ad

Advertisements

J

Jon Peltier

Try:

Set Cht = ThisWorkbook.Charts.Add

No promises, but I suspect not referencing the workbook reduces the reliability of
the code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Ad

Advertisements

G

Guest

Tried this and no difference. I ended up using an embedded chart,
ChartObjects, and then moving the chart to its own worksheet. This is an
extra step, but works.

Thanks for your assistance.
4THE1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top