macro to graph values

[clintonf]

Hi all,

I need assistance troubleshooting my graphing macro.

The specific message that I am getting is a message box stating:
In the box title bar: "Ole Dispatch Exception in Worksheet Open Event Handler"
In the body: "Activate method of Window class failed"

My macro takes data from worksheet X and places the graph in worksheet X.

The relevant code looks like:

Sub graphIt(myRange As String, ws As Worksheet)
    Dim newChart As Chart
    Dim chartName As String
    
    Set newChart = Charts.Add 
    newChart.ChartType = xlBarStacked
    newChart.name = ws.name & "_Chart" 
    
    newChart.SetSourceData Source:=ws.Range(myRange), PlotBy:=xlRows
    
    newChart.Location Where:=xlLocationAsObject, name:=ws.name '<-- Error thrown here!
   MsgBox "I occur after the error!"

    chartName = ws.ChartObjects(ws.ChartObjects.Count).name
    ws.ChartObjects(chartName).Activate

    'Some more chart formatting here
End Sub

After throwing the error the macro execution continues to the end.

I'm not sure what the error is telling me as I'm not explicitly calling activate where the error is being thrown. I have tried moving code around, eg. putting the Location() call at the bottom of the sub-routine but am still getting the error.

Any help is appreciated.

Thanks!

Thanks!

[Leith Ross]

Hello clintonf,

There are 2 type of Charts: Chart Objects and Chart Sheets. you are mixing the 2 in your code. My assumption is you want to create an embedded chart on a particular sheet and then move it to another worksheet. The code below will create an embedded Bar Stacked chart on "Sheet1" and move it to "Sheet2". The source range is on "Sheet1", A1:A10.

Create and Move Embedded Chart

Sub AddBarChart()

  Dim Chrt As Object
  
    Set Chrt = ActiveSheet.ChartObjects.Add(Top:=50, Left:=100, Width:=400, Height:=200)
      With Chrt.Chart
        .ChartType = xlBarStacked
        .HasLegend = True
        .SetSourceData Source:=Range("A1:A10")
        .HasTitle = True
        .ChartTitle.Text = "My Chart"
      End With
    
    Chrt.Select
    Chrt.Chart.Location xlLocationAsObject, "Sheet2"
    
End Sub

Sincerely,
Leith Ross

[clintonf]

Hi Leith,

Your suggestion worked perfectly.

Thanks very much for your help!