Yesterday duncanslam (Steve Duncan) posted in Excel.misc for a way to
hide charts that don't have data. Seemed doable to me, so I had a go
only to discover the nasty problems associated with returning the
address of a chart's data.
Andy Pope cleared most of that up for me, thanks again Andy, so I then
had a go and thought I figured out a way of hiding dataless embedded
charts...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cht As ChartObject
Dim strSeries As String
For Each Cht In ActiveSheet.ChartObjects
On Error GoTo BLANK_CHART
strSeries = Cht.Chart.SeriesCollection(1).Formula
Cht.Visible = True
GoTo ALL_CHARTS
BLANK_CHART: Cht.Visible = False
ALL_CHARTS: Next Cht
End Sub

I had three XY scatter charts on the sheet, all with their own separate
X and Y values, and when I deleted the data (either X, Y or both), the
affected chart became invisible.

Unfortunately, when I delete the data of a second chart it results in
the Run-time error 1004 Unable to get the Formula Property of the
Series Class.

If I click End on the error message dialog the second (dataless) chart
is invisible, so at least the code worked.

So it seems to me like the On Error GoTo BLANK_CHART line somehow fails
for the second dataless chart.

Is there anyway around this problem?


Ken Johnson