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

Hi,

Try this revision to your code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cht As ChartObject
Dim strSeries As String
Dim blnSeeChart As Boolean

On Error GoTo BLANK_CHART
For Each Cht In ActiveSheet.ChartObjects
blnSeeChart = True
strSeries = Cht.Chart.SeriesCollection(1).Formula
Cht.Visible = blnSeeChart
Next Cht

Exit Sub

BLANK_CHART:
blnSeeChart = False
Resume Next

End Sub

Cheers
Andy

Ken Johnson wrote:
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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

that is absolutely fantastic!

Correct me if I'm wrong (I usual am)...

It looks to me like the solution involves removing the treatment of the
error from the inside of the loop. I wasn't aware of the need for
Resume Next, I had hoped that On Error GoTo could be repeatedly used
inside the loop.

Anyway, I still a bit confused, but excited that it now works.

Thanks again Andy.

Ken Johnson

Hi Ken,

The 'On Error Statement' help explains, much better than I could, why I
had to re-jig the error handling.

Cheers
Andy

Ken Johnson wrote:
Hi Andy,

that is absolutely fantastic!

Correct me if I'm wrong (I usual am)...

It looks to me like the solution involves removing the treatment of the
error from the inside of the loop. I wasn't aware of the need for
Resume Next, I had hoped that On Error GoTo could be repeatedly used
inside the loop.

Anyway, I still a bit confused, but excited that it now works.

Thanks again Andy.

Ken Johnson


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks Andy, I'll have a look.

Ken Johnson