Hide chart until you have 2 or more data points to plot 2

[MeGustaXL]

Hi All.

I'm developing a worksheet in which the user enters (or pastes) data into column A, the plot points are calculated in (hidden) columns B to E, and the results are shown in an embedded chart object.

All works fine if the chart is showing; as each new point is added, the dynamic named ranges expand and the chart updates.

The problem is that with only 1 or 0 data points, the chart labels show #NUM! or #DIV/0! error, which looks messy.

I've tried using the WorksheetChange event to move the Chart Object to another sheet if the number of points is 0 or 1, then put it back on Sheet1 when there are 2.

This works fine if you start with no data and the chart hidden. If you put in data one by one, the chart pops up as soon as you enter the second data point.
But if the chart is already showing, the macro stops with a "400" error at that point.
Also, if you have only 1 point, then delete it, the macro tries to hide the already "hidden" chart: 400 error.

Is there some way of setting the "Visible" property of an embedded chart object?

Please help, cos I'm going bananas with this!

 

[Sylv4n]

Not having tested this:

I would guess at.

To stop it calculatig on less than two points I would add something like:

If Len(Cells(&quot;A&quot;,3)) <> 0 then
Code to update the chart
End if

Where Cell &quot;A3&quot; is your third point so, if there is no data in the third point then it will not update your chart.

As far as keeping the chart hidden, I'm not sure.
If the chart is on its own sheet, then there should be something like:

Sheets(&quot;ChartSheet&quot.Hide - or visible = false

and if it is on alother sheet with other stuff, try:

Sheets(&quot;TheSheetName&quot.ChartName.Hide - or Visible = false

As I said, All unested just theory

 

[RobBroekhuis]

Rather than fiddle with the chart visibility property, consider constructing your chart in such as way that the error values don't appear. For example, by putting conditional formulas in the first two cells of your plot range that default to 0, or 1, or some other appropriate value, until a proper value becomes available. You can also use your event handler to turn on/off a label on the chart that tells the user if enough data has been entered yet.


Rob

 

[MeGustaXL]

Thanks Guys, both helpful suggestions for various other projects!

Sylv4n: This worksheet doesn't have a calculating routine, because the data points are held in Named Ranges which expand/contract along with the data in column A, then the chart series is set to those Names. However, your tip for hiding an entire sheet set my mind racing on another project where I've been trying to do just that! TYVVM

Rob: I've already got some of those, but now I've changed them so that if an &quot;A&quot; cell is blank, the B...E cells say #N/A:

For example:
=IF(ISNUMBER(A1),A1*2,NA()) seems to work.

Both: I resorted to a bit of a cheat's way out. First I test to see if there are less than 2 data points, and if there are, do this:

ActiveSheet.ChartObjects(&quot;Plot&quot.Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes(&quot;Plot&quot.Height = 0
ActiveSheet.Shapes(&quot;Plot&quot.Width = 0

This shrinks the chart object to a dot, which is invisible against the dark background I intend to apply.

If there are 2 or more, it does this:

ActiveSheet.ChartObjects(&quot;Plot&quot.Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes(&quot;Plot&quot.Top = Range(&quot;H2&quot.Top
ActiveSheet.Shapes(&quot;Plot&quot.Left = Range(&quot;H2&quot.Left
ActiveSheet.Shapes(&quot;Plot&quot.Height = 350
ActiveSheet.Shapes(&quot;Plot&quot.Width = 400

Which anchors the top left of the chart on cell H2, then resizes it.

Now, this works great, but it seems a bit clumsy to do it every single time the WorkSheet_Change event fires: any suggestions how I can refine this?

 

[Sylv4n]

Yes,
Before you change the Height or Width do something like:

IF ActiveSheet.Shapes(&quot;Plot&quot.Width <> 0 THEN

ActiveSheet.ChartObjects(&quot;Plot&quot.Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes(&quot;Plot&quot.Height = 0
ActiveSheet.Shapes(&quot;Plot&quot.Width = 0

END IF

And:

IF ActiveSheet.Shapes(&quot;Plot&quot.Width = 0 THEN

ActiveSheet.ChartObjects(&quot;Plot&quot.Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes(&quot;Plot&quot.Top = Range(&quot;H2&quot.Top
ActiveSheet.Shapes(&quot;Plot&quot.Left = Range(&quot;H2&quot.Left
ActiveSheet.Shapes(&quot;Plot&quot.Height = 350
ActiveSheet.Shapes(&quot;Plot&quot.Width = 400

END IF

This way it will only ajust it if it is not the correct size

 

[MeGustaXL]

Absolutely Brilliant!

Thank you very much - now the chart instantly collapses when I delete the data, and only expands once, whether I paste a column of 500 items or just add them one at a time - just as I wanted it to!

Great Big Kisses,

CB