Hiding & Unhiding graphs...
 

Hello -

I've got a sheet that contains 4 graphs -- the graphs are already
built and update fine. Rather than have 4 small graphs or make users
switch sheets, I want to use a spin-button to let them scroll through
the graphs.

I've inserted the spin-button and linked it to cell T2 -- available
values are 1-4 and is working properly. However, I can get the graphs
to hide/unhide when T2 changes. I tried this out on two of the
graphs, code below:
***************
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$t$2" Then
Application.EnableEvents = False
If Target.Value = 1 Then
ActiveSheet.ChartObjects("Chart 1").Visible = True
ActiveSheet.ChartObjects("Chart 7").Visible = False
Else
ActiveSheet.ChartObjects("Chart 7").Visible = True
ActiveSheet.ChartObjects("Chart 1").Visible = False
End If
Application.EnableEvents = True
End If

*****************

What am I doing wrong? Remember that I also need to modify the code
to rotate through all 4 of the graphs.

AND, is there a way to make the the spin-button 'scroll' through the
values? So, it would go 1-2-3-4-1-2-3-4? Right now, it only goes
1-2-3-4-3-2-1-2...

TIA,
Ray

Hiding & Unhiding graphs...
 

Are all 4 charts different charts or do you plot each area depending on the
value of the spinner? I'm not a 100% sure what your data area looks like,
but one way to do this is to set up a single range for plotting whose
formulas change to pick up the data for the appropriate chart. You can use
an OFFSET, INDEX or INDIRECT function for this purpose. Then the spinner
value controls one of the arguments for these functions, picking up the
appropriate data. Now to hide the chart all together will be easy because
there is only one chart.

It sounds like your charts overlay each other, that would not be necessary
with the above approach. Also the above means there is no code necessary
except if you don't want to see any charts.


--
Cheers,
Shane Devenshire


"Ray" wrote:

Hello -

I've got a sheet that contains 4 graphs -- the graphs are already
built and update fine. Rather than have 4 small graphs or make users
switch sheets, I want to use a spin-button to let them scroll through
the graphs.

I've inserted the spin-button and linked it to cell T2 -- available
values are 1-4 and is working properly. However, I can get the graphs
to hide/unhide when T2 changes. I tried this out on two of the
graphs, code below:
***************
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$t$2" Then
Application.EnableEvents = False
If Target.Value = 1 Then
ActiveSheet.ChartObjects("Chart 1").Visible = True
ActiveSheet.ChartObjects("Chart 7").Visible = False
Else
ActiveSheet.ChartObjects("Chart 7").Visible = True
ActiveSheet.ChartObjects("Chart 1").Visible = False
End If
Application.EnableEvents = True
End If

*****************

What am I doing wrong? Remember that I also need to modify the code
to rotate through all 4 of the graphs.

AND, is there a way to make the the spin-button 'scroll' through the
values? So, it would go 1-2-3-4-1-2-3-4? Right now, it only goes
1-2-3-4-3-2-1-2...

TIA,
Ray