Cycle thru Graphs

[Ray]

Hello -

I thought I found an answer for this problem, but can't quite make it
work ....

As part of a large package of information, I have a ;dynamic' chart
sheet where users can change one (or more) criteria to see the level
of detail they want. Four graphs are created from this input. This
part of my code works fine and is not included in my sample code
below...

Here's the problem -- I only want ONE of the four graphs to be visible
at a time. Currently, I'm using 4 option_buttons to allow the user to
select the graph they'd like to see. The graph called by option1
should be the 'default' (ie visible on sheet_activate). Here's the
code so far ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If OptionButton1.Value = True Then
ActiveSheet.ChartObjects("wkly_visitors").Visible = True

ElseIf OptionButton2.Value = True Then
ActiveSheet.ChartObjects("wkly_sales").Visible = True

ElseIf OptionButton2.Value = True Then
ActiveSheet.ChartObjects("wkly_customer").Visible = True

ElseIf OptionButton2.Value = True Then
ActiveSheet.ChartObjects("wkly_convrate").Visible = True

End If
End Sub

The code to update the graph has it's own macro and is called by a
click_event.

What am I doing wrong with the code above?

TIA, Ray

 

[John Coleman]

(Untested) Maybe you can try explicitly setting the other graphs to be
invisible

e.g.

If OptionButton1.Value = True Then
ActiveSheet.ChartObjects("wkly_visitors").Visible = True
ActiveSheet.ChartObjects("wkly_sales").Visible = False
ActiveSheet.ChartObjects("wkly_customer").Visible =False
ActiveSheet.ChartObjects("wkly_convrate").Visible = True

etc.

(or - to cut down on code clutter you can create 4 boolean visibility
variables, one for each chart, set these variables in the if-then-else
block and then assign these values to the visibility properties
afterwards

Hope that helps

 

[John Coleman]

(Untested) Maybe you can try explicitly setting the other graphs to be
invisible

e.g.

If OptionButton1.Value = True Then
ActiveSheet.ChartObjects("wkly_visitors").Visible = True
ActiveSheet.ChartObjects("wkly_sales").Visible = False
ActiveSheet.ChartObjects("wkly_customer").Visible =False
ActiveSheet.ChartObjects("wkly_convrate").Visible = True

obviously, I meant

ActiveSheet.ChartObjects("wkly_convrate").Visible = False

 

[Peter T]

Hi Ray,

Looks like you have ActiveX optionbuttons.
In the worksheet module -

Private Sub OptionButton1_Click()
ChartVis "wkly_visitors"
End Sub

Private Sub OptionButton2_Click()
ChartVis "wkly_sales"
End Sub

Private Sub OptionButton3_Click()
ChartVis "wkly_customer"
End Sub

Private Sub OptionButton4_Click()
ChartVis "wkly_convrate"
End Sub

Sub ChartVis(sName As String)
Dim i As Long
Dim vArr
vArr = Array("wkly_visitors", "wkly_sales", "wkly_customer",
"wkly_convrate")
For i = LBound(vArr) To UBound(vArr)
With ActiveSheet.ChartObjects(vArr(i))
..Visible = .Name = sName
End With
Next

End Sub

Sub ChartVis() could go in a normal module if you want to call it from other
routines. Would probably want to include some error handling in case of non
existent chart name(s).

Regards,
Peter T

 

[Ray]

Hi Ray,

Looks like you have ActiveX optionbuttons.
In the worksheet module -

Private Sub OptionButton1_Click()
ChartVis "wkly_visitors"
End Sub

Private Sub OptionButton2_Click()
ChartVis "wkly_sales"
End Sub

Private Sub OptionButton3_Click()
ChartVis "wkly_customer"
End Sub

Private Sub OptionButton4_Click()
ChartVis "wkly_convrate"
End Sub

Sub ChartVis(sName As String)
Dim i As Long
Dim vArr
vArr = Array("wkly_visitors", "wkly_sales", "wkly_customer",
"wkly_convrate")
For i = LBound(vArr) To UBound(vArr)
With ActiveSheet.ChartObjects(vArr(i))
.Visible = .Name = sName
End With
Next

End Sub

Sub ChartVis() could go in a normal module if you want to call it from other
routines. Would probably want to include some error handling in case of non
existent chart name(s).

Regards,
Peter T

Thanks for the input Peter ... it works now ... kinda....

So, now the graphs appear one at a time, but they don't update at the
right 'time'. I mean, if option1 is selected (and graph1 is visible),
and then option2 is selected, the graphs don't update UNTIL another
cell is selected. It seems that the macro doesn't see the
option_button changes as a worksheet_change event.

Any ideas how to fix this....?

 

[Ray]

Thanks for the input Peter ... it works now ... kinda....

So, now the graphs appear one at a time, but they don't update at the
right 'time'. I mean, if option1 is selected (and graph1 is visible),
and then option2 is selected, the graphs don't update UNTIL another
cell is selected. It seems that the macro doesn't see the
option_button changes as a worksheet_change event.

Any ideas how to fix this....?

Looks like there was some timing issues to a couple of posts -- sorry
to get your responses switched up! Thanks to both John & Peter for
your time and input...

Peter, your solution works perfectly, thanks very much! Could you
explain what this line means (from Sub ChartVis)
..Visible = .Name = sName

br//ray

 

[Peter T]

Peter, your solution works perfectly, thanks very much! Could you
explain what this line means (from Sub ChartVis)
.Visible = .Name = sName

br//ray

The code could have been written like this
Dim bFlag as boolean
'in the loop
bFlag = ActiveSheet.ChartObjects(vArr(i)).Name = sName
ActiveSheet.ChartObjects(vArr(i)).Visible = bFlag

or
If ActiveSheet.ChartObjects(vArr(i)).Name = sName then
ActiveSheet.ChartObjects(vArr(i)).Visible = True
else
ActiveSheet.ChartObjects(vArr(i)).Visible = False
end if

Can use With... End With to read the .Name property, compare it with sName
to evaluate a true/false expression in turn to apply to the .Visible
property all in one go.

If needs you could maybe call your macro to update your chart(s) from the
Option buttons, eg in each button click

myChartUpdateMacro "chartName"

Sub myChartUpdateMacro (sName as String)
'code to update chart named sName
' call ChartVis here, not from the button click
ChartVis sName

myChartUpdateMacro & ChartVis probably in a normal module.

Regards,
Peter T