Multiple Worksheet_Change in a sheet?

[KiaOrana]

Hi guys,

I have created a dashboard in which I have 30 or so graphs. I would like some of the graphs (14 in total) to display only if they meet a condition chosen by two drop downs cell.

In the first drop down cell (let's say A1), the user will chose a day of the week, and depending of that day, the matching graph will display. That is what's being done in the first statement below

Then, in the second drop down (here A50), the user selects another day of the week (independant from the first one) and the graphs will display accordingly. This is my second statement below

However, I came up with two Worksheet_Change which does not seem to work. It seems that I need to combine my two statements. Do you have any ideas how I cold do so?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedChart As Range
Dim blnVisible As Boolean


 Set SelectedChart = Range("A1")
    
    blnVisible = SelectedChart.Value = ""
    
' Make first all charts invisible
    ActiveSheet.ChartObjects("Monday").Visible = blnVisible
    ActiveSheet.ChartObjects("Tuesday").Visible = blnVisible
    ActiveSheet.ChartObjects("Wednesday").Visible = blnVisible
    ActiveSheet.ChartObjects("Thursday").Visible = blnVisible
    ActiveSheet.ChartObjects("Friday").Visible = blnVisible
    ActiveSheet.ChartObjects("Saturday").Visible = blnVisible
    ActiveSheet.ChartObjects("Sunday").Visible = blnVisible
    ActiveSheet.ChartObjects("Average").Visible = blnVisible
    
    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedChart As Range
Dim blnVisible As Boolean


 Set SelectedChart = Range("A50")
    
    blnVisible = SelectedChart.Value = ""
    
' Make first all charts invisible
    ActiveSheet.ChartObjects("EGMs in Use on Monday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Tuesday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Wednesday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Thursday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Friday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Saturday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use on Sunday").Visible = blnVisible
    ActiveSheet.ChartObjects("EGMs in Use Week Overview").Visible = blnVisible
    
    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True

End Sub

Many Thanks!

 

[Smitty]

Welcome to the Board!

I'm heading out for the day, so this is imcomplete, but it should give you an idea about how to combine the two events with a Select Case statement:

<font face=Calibri><SPAN style="color:#00007F">Public</SPAN> SelectedChart <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Public</SPAN> blnVisible <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1,A50")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address<br>                <SPAN style="color:#00007F">Case</SPAN> "$A$1"<br>                <SPAN style="color:#007F00">' Make first all charts invisible</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                        .ChartObjects("Monday").Visible = blnVisible<br>                        .ChartObjects("Tuesday").Visible = blnVisible<br>                        .ChartObjects("Wednesday").Visible = blnVisible<br>                        .ChartObjects("Thursday").Visible = blnVisible<br>                        .ChartObjects("Friday").Visible = blnVisible<br>                        .ChartObjects("Saturday").Visible = blnVisible<br>                        .ChartObjects("Sunday").Visible = blnVisible<br>                        .ChartObjects("Average").Visible = blnVisible<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> "$A$50"<br>                    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                        .ChartObjects("EGMs in Use on Monday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Tuesday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Wednesday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Thursday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Friday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Saturday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use on Sunday").Visible = blnVisible<br>                        .ChartObjects("EGMs in Use Week Overview").Visible = blnVisible<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[KiaOrana]

Thanks a lot Smitty!

I am not too sure how to complete it, I'll give it a go, but being a complete newbie at VBA, I'm not too sure how to work it out.

Thanks again!http://www.mrexcel.com/forum/member.php?u=18403

 

[Smitty]

I'll be buried tomorrow, but post back with how it goes and I'll check in if I can.

 

[kaldaru]

*Sorry I might have double posted but I can't find the previous one!


Hello!




I know it is an old thread, but my problem is very similar to Kia's. As obvious at it seems, I'm a pure VBA newbie!


I am building a dashboard and I need to put lots of graph in it.


That is why I used VBA to be able to show/hide some of them in a part of my dashboard.


I used simple VBA lines to do so.


Now I would like to repeat the same opeation for another part of my dashboard. I face the same worksheet_change
problem and I don't really know how to combine two events.






Thanks in advance!


Hugo






PS:This is basically what does my simple line is to show/hide graphs:






Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedChart As Range
Dim blnVisible As Boolean




Set SelectedChart = Range("G5")

blnVisible = SelectedChart.Value = ""

' Make first all charts invisible
ActiveSheet.ChartObjects("Test1").Visible = blnVisible
ActiveSheet.ChartObjects("Test2").Visible = blnVisible
ActiveSheet.ChartObjects("Test3").Visible = blnVisible




If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True


End Sub