Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
(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 On Feb 27, 6:27 am, "Ray" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
On Feb 27, 7:21 am, "John Coleman" wrote:
(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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
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" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
On Feb 27, 7:28 am, "Peter T" <peter_t@discussions wrote:
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" wrote in message ups.com... 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 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....? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
On Feb 27, 7:36 am, "Ray" wrote:
On Feb 27, 7:28 am, "Peter T" <peter_t@discussions wrote: 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" wrote in message oups.com... 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru Graphs
"Ray" wrote in message ups.com... On Feb 27, 7:36 am, "Ray" wrote: On Feb 27, 7:28 am, "Peter T" <peter_t@discussions wrote: 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 <snip 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a graph with 2-cycle X 3-cycle log-log graph paper? | Charts and Charting in Excel | |||
Cycle Times | Excel Worksheet Functions | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
Automatic Graphs/ Dynamic Graphs | Charts and Charting in Excel | |||
Cycle through all worksheets | Excel Programming |