Help with Excel Chart 1

[sjh]

Hi,
I have several questions regarding Excel charts?
1) In the VBA code, how can I name the chart that I just added?

2) What is the code for deleting a chart on a worksheet?

3) ActiveChart.SetSourceData Source:=mySheet.Range("A10:A12,C10:C12&quot, PlotBy:=xlColumns

I would like to assign Range using the Cells(). How can I specify ranges that are not next to each other? ....like Range(Cells(10,1),Cells(12,1), Cells(10,3),Cells(12,3))

Thank you

 

[SkipVought]

sjh,

Immediately after adding any object, the object is active. At that point you can assign values to several properties most effectively using the With...End With statement like this

    Set wsThis = ActiveSheet
    Set rng1 = Range(Cells(1, 1), Cells(3, 1))
    Set rng2 = Range(Cells(1, 3), Cells(3, 3))
    Charts.Add
    With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Range(rng1, rng2), PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:=wsThis.Name
    End With
    ActiveSheet.ChartObjects(1).Name = "MyChart"
...
    ActiveSheet.ChartObjects("MyChart").Delete

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Hi Skip,

Thank you for your post! I have additional question. Is there a way to check if the chart exists on the active sheet?

Thank you,
SJH

 

[sjh]

Hello again,

I found out that I can use this <ActiveSheet.ChartObjects.Delete> to delete all the charts, so that answers my previous question.

Now, I am trying to align the charts (pie chart and bar chart) on a worksheet. I tried the following code, but I get an error with the .LEFT line.

With Worksheets(&quot;Budget Report&quot
'.ChartObjects(&quot;BarChart&quot.Left = .Columns(&quot;B&quot.Left
.ChartObjects(&quot;BarChart&quot.Top = 200
End With

The .TOP property correctly puts the bar chart at 200, but after I add a Pie chart, the bar chart gets pushed to the bottom. Any idea why?

With Worksheets(&quot;Budget Report&quot
'.ChartObjects(&quot;PieChart&quot.Left = .Columns(&quot;B&quot.Left
.ChartObjects(&quot;PieChart&quot.Top = 600
End With

 

[SkipVought]

Yes, a number of ways.

    For Each co In ActiveSheet.ChartObjects
      MsgBox "I have at least one chart on this sheet"
    Next

or

  on error resume next
  with activesheet.Chartobjects(1)
    if err.number <> 0 then goto NoChart
...
  end with
...
  exit sub
NoChart:
'no chart error trap
...

Hope this helps.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com