Charts.Add fails with Workbook_Open

[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.

 

[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/
_______

 

[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