Does anybody know how I can have a chart show on a spreadsheet only if there
is data? I have a number of charts on a single worksheet representing weeks
of data. If there was no data for a week, I'd rather not show the empty
chart. Is there a way?
--
Steve Duncan,

Hi Duncan,

try out this macro on a copy of your sheet with embedded charts

Public Sub HideBlankCharts()
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

If you are not sure what to do with the code then follow these steps to
get it into place in the VB Editor...

1. Copy the 11 lines of code above

2. Go to your open Excel workbook and press Alt + F11. This takes you
to the VB Editor (You can also get there by going Tools|Macro|Visual
Basic Editor)

3. With the main menu at the top of the of the VB Editor go
Insert|Module.

4. Paste the code into the module (white space) that appears, then
Save.

5. Press Alt + F11 to get back to the usual Excel interface (You can
also get there by going File|Close and Return to Microsoft Excel)

Before you can run the macro, your workbook security level must be set
to Medium.
If it is not already set at Medium then...

1. Go Tools|Macro|Security... click on Medium
2. Save
3. Close
4. Open. Click on "Enable Macros" on the "Security Warning" dialog that
appears.


To run the macro you can go...Tools|Macro|Macros...look for
"HideBlankCharts" in the list of macros on the Macro dialog, click its
name in the list then click "Run" (or, you can just double click its
name). This is a tedious way of running a macro, so you might want to
use a keyboard shortcut. To do that go..Tools|Macro|Macros... select
the macro from the list on the Macro dialog, then click Options and
type in an appropriate key combination.

Another way of using the macro is to have it run automatically by Excel
everytime the user changes any values on the sheet with the data and
charts.

If you prefer that, then copy the following code (it's exactly the same
code inside a Worksheet_Change Event Procedure which has to be pasted
into the code module belonging to the sheet with the data and charts),
then in Excel (normal interface) right click the worksheet's sheet tab
then choose "View Code" from the popup menu. This takes you straight to
the code module that detects certain events that occur on the
worksheet. Paste the code, Save, then go Alt + F11 to return to Excel.

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

Ken Johnson

Ken Johnson wrote:
Hi Duncan,
When he should have written Hi Steve:-/

Ken Johnson

Hi Steve,

Sadly, further testing of my solution has revealed a flaw. An error
message results when more than one of the charts is lacking data.
Clicking "End" on the Error Message dialog gets rid of the dialog and
all charts with no data are invisible, however it is far from
satisfactory having to put up with that. I'm trying to resolve the
problem and will reply back here when I have succeeded.

Ken Johnson

Hi Steve,

Good news!

Andy Pope has revised my code and fixed it up...

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

Next time you have a question about charts try
microsoft.public.excel.charting where charting genius Andy Pope will
find your solution in almost an instant.

Ken Johnson