Checking if a Chart Exists on a Sheet

  • Thread starter Thread starter Keith Wilby
  • Start date Start date
K

Keith Wilby

Newbie question:

What code do I need to check if a chart exists on a worksheet?

Many thanks.

Keith.
 
Keith Wilby said:
Newbie question:

What code do I need to check if a chart exists on a worksheet?
Click to expand...

Whoops, how do I determine it's name if one exists? I've tried
ActiveSheet.ChartObjects.ActiveChart.Name but that's obviously not right.
 
Hi
Charts are contained inside ChartObjects. Chartobjects have names
(which you don't see on the "chart") while Charts have titles (which
you do see).
If you only have one chart on a sheet then it is contained inside one
ChartObject and you can do

myName = ActiveSheet.ChartObject(1).Name
myTitle = ActiveSheet.ChartObject(1).Chart.Title
to get name and title.

To see if a chart exists try

On Error resume next
Set myChartObject = Activesheet.ChartObjects(myName)
If myChartObject is Nothing then
 
Sub test()
Dim i As Long
Dim s As String
Dim ws As Worksheet

Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
s = s & ws.ChartObjects(i).Name & vbCr
Next

If Len(s) = 0 Then s = "No Charts on " & ws.Name

MsgBox s
End Sub

Regards,
Peter T
 
Sorry, post went when I pressed return for some reason. Last bit
should be

On Error resume next
Set myChartObject = Activesheet.ChartObjects(myName)
If myChartObject is Nothing then
'some code
End if
On error goto 0

where myName is a text string.
If you have several charts with Titles but the parent chartobjects do
not have names try

For Each Cht In Activesheet.ChartObjects
If Cht.Chart.Title = myTitle then
msgbox "Chart Exists!"
Exit For
end if
next Cht

regards
Paul
 
One more:

If ActiveSheet.ChartObjects.Count > 0 Then
MsgBox "has some!"
Else
MsgBox "nope"
End If
 
Back
Top