Making a Chart "Visible"
EE Pros.;
I have a macro that when fired, it exposes a set of Sheets and two Charts. I have no problem with the Sheets or Charts showing up
Here is the code;
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating
Select Case Target.Address
Case "$N$17"
If [n17].Value = "" Then Exit Sub
On Error Resume Next
Sheets("FUN_Assessment").V
Sheets("GWS Questionaire").Visible = xlSheetHidden: Sheets("Heatmaps").Visible
Sheets("Maturity Model").Visible = xlSheetHidden: Sheets("MaturityChart").Vi
Sheets("CA_Questionaire").
Sheets("CA_Priority_Heatma
Charts("CA_Summary_Scores"
Sheets("GWS_Process_Survey
Sheets("Radar_Chart").Visi
Select Case Sheets("Splash").Range("N1
Case Is = Range("$N$17").Value
Sheets("CA_Questionaire").
End Select
On Error GoTo 0
Application.ScreenUpdating
End Select
If Target.Address = "$N$17" Then
Sheet2.Unprotect Password:="jam"
Worksheets("Question_DB").
Application.GoTo Worksheets("CA_Questionair
End If
End Sub
What I have a problem with is when I go to hide all of the sheets and 2 charts. The Charts do not go hidden.
I think I'm doing something wrong with the way I have the Charts identified in the line:
Charts("CA_Summary_Scores"
Thank you in advance,
B.
I have a macro that when fired, it exposes a set of Sheets and two Charts. I have no problem with the Sheets or Charts showing up
Here is the code;
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating
Select Case Target.Address
Case "$N$17"
If [n17].Value = "" Then Exit Sub
On Error Resume Next
Sheets("FUN_Assessment").V
Sheets("GWS Questionaire").Visible = xlSheetHidden: Sheets("Heatmaps").Visible
Sheets("Maturity Model").Visible = xlSheetHidden: Sheets("MaturityChart").Vi
Sheets("CA_Questionaire").
Sheets("CA_Priority_Heatma
Charts("CA_Summary_Scores"
Sheets("GWS_Process_Survey
Sheets("Radar_Chart").Visi
Select Case Sheets("Splash").Range("N1
Case Is = Range("$N$17").Value
Sheets("CA_Questionaire").
End Select
On Error GoTo 0
Application.ScreenUpdating
End Select
If Target.Address = "$N$17" Then
Sheet2.Unprotect Password:="jam"
Worksheets("Question_DB").
Application.GoTo Worksheets("CA_Questionair
End If
End Sub
What I have a problem with is when I go to hide all of the sheets and 2 charts. The Charts do not go hidden.
I think I'm doing something wrong with the way I have the Charts identified in the line:
Charts("CA_Summary_Scores"
Thank you in advance,
B.
ASKER
Andrewssd3,
Thanks for the response;
1.) The "On Error Resume Next" is in the code. Take a look.
2.) All worksheets are not hidden. One sheet called "Splash" is not hidden and is the only tab that should be visible.....and is.
3.) When I added "Charts" it worked to show the Charts, but when I tried to "hide" them, they all hid except the two charts.
An easier way to answer this question is, how do you identify "Charts" vs. "Sheets"?
Showing the Charts I use: Charts("CA_Detailed_Result
The Hiding Charts I use: Charts("CA_Summary_Scores"
The problem is, when I fire the macro by entering a value in "N17" I get all the right tabs, including the Charts. But when I clear them, all tabs are hidden except the Charts.
Make sense?
B.
Thanks for the response;
1.) The "On Error Resume Next" is in the code. Take a look.
2.) All worksheets are not hidden. One sheet called "Splash" is not hidden and is the only tab that should be visible.....and is.
3.) When I added "Charts" it worked to show the Charts, but when I tried to "hide" them, they all hid except the two charts.
An easier way to answer this question is, how do you identify "Charts" vs. "Sheets"?
Showing the Charts I use: Charts("CA_Detailed_Result
The Hiding Charts I use: Charts("CA_Summary_Scores"
The problem is, when I fire the macro by entering a value in "N17" I get all the right tabs, including the Charts. But when I clear them, all tabs are hidden except the Charts.
Make sense?
B.
Not for points, but suggest you change this snippet of code:
to this:
you can use the same approach for those sheets you want to hide.
pushing multiple lines of code on one line is not a good practice as its hard to read and debug.
This is one case where more is better - indenting, blank lines separating thoughts, etc., help create better, easier to read and debug apps.
Cheers,
Dave
Sheets("CA_Questionaire").Visible = xlSheetVisible: Sheets("CA_Prioritization").Visible = xlSheetVisible: Sheets("CA_Priority_Heatmap").Visible = xlSheetVisible: Sheets("Maturity Model").Visible = xlSheetVisible: Sheets("Radar Chart").Visible = xlSheetVisible: Sheets("100%Chart").Visible = xlSheetVisible: Sheets("Priorities").Visible = xlSheetVisible: Charts("CA_Detailed_Results").Visible = xlSheetVisible: Charts("CA_Summary_Scores").Visible = xlSheetVisibleto this:
Dim shtVisible As String
shtVisible = "CA_Questionaire,CA_Prioritization,CA_Priority_Heatmap,Maturity Model,Radar Chart,100%Chart,Priorities," & _
"CA_Detailed_Results,CA_Summary_Scores"
Sheets(Split(shtVisible, ",")).Visible = xlSheetVisibleyou can use the same approach for those sheets you want to hide.
pushing multiple lines of code on one line is not a good practice as its hard to read and debug.
This is one case where more is better - indenting, blank lines separating thoughts, etc., help create better, easier to read and debug apps.
Cheers,
Dave
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Andrewssd3 and Dave,
I've cleaned up the code and here's the story. On the "Splash" Screen in cell N17, I have a validation box that selects from a set of templates. When a selection is made, the Private Sub Worksheet_Change(ByVal Target As Range) Macro should display the Tabs that are listed as "visible". It doesn't matter which Template is selected, the Tabs that become visable should appear. For some reason, by the code below, when I make a selection in N17, I do not get any change to the WB (i.e. no new Tabs visable). I believe the problem may be in how I've declared N17 value............. or case...............
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating
Select Case Target.Address
Case Is = "$N$17"
If [n17].Value = "" Then Exit Sub
On Error Resume Next
Dim shtVisible As String
Select Case Sheets("Splash").Range("N1
Case Is = Range("$N$17").Value
shtVisible = "CA_Questionaire,CA_Priori
"CA_Detailed_Results,CA_Su
Sheets(Split(shtVisible, ",")).Visible = xlSheetVisible
End Select
On Error GoTo 0
Application.ScreenUpdating
End Select
If Target.Address = "$N$17" Then
Sheet2.Unprotect Password:="pass"
Worksheets("Question_DB").
Application.GoTo Worksheets("CA_Questionair
End If
End Sub
I've cleaned up the code and here's the story. On the "Splash" Screen in cell N17, I have a validation box that selects from a set of templates. When a selection is made, the Private Sub Worksheet_Change(ByVal Target As Range) Macro should display the Tabs that are listed as "visible". It doesn't matter which Template is selected, the Tabs that become visable should appear. For some reason, by the code below, when I make a selection in N17, I do not get any change to the WB (i.e. no new Tabs visable). I believe the problem may be in how I've declared N17 value............. or case...............
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating
Select Case Target.Address
Case Is = "$N$17"
If [n17].Value = "" Then Exit Sub
On Error Resume Next
Dim shtVisible As String
Select Case Sheets("Splash").Range("N1
Case Is = Range("$N$17").Value
shtVisible = "CA_Questionaire,CA_Priori
"CA_Detailed_Results,CA_Su
Sheets(Split(shtVisible, ",")).Visible = xlSheetVisible
End Select
On Error GoTo 0
Application.ScreenUpdating
End Select
If Target.Address = "$N$17" Then
Sheet2.Unprotect Password:="pass"
Worksheets("Question_DB").
Application.GoTo Worksheets("CA_Questionair
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bright, when you have problems like this, its time to try debugging. Select line 6 then hit F9, then toggle over and make a change, then it should break at line 6 and you can step through the code with F8 to see where the bouncing YELLOW line telling you where your code is about to run. You can display the immediate window with VIEW->IMMEDIATE WINDOW if its not already displayed in your VBA editor. You can then type things like:
debug.print Range("N17").value
or other commands to determine what the state of variables are, test sheet values, etc.
Cheers,
Dave
debug.print Range("N17").value
or other commands to determine what the state of variables are, test sheet values, etc.
Cheers,
Dave
Idle curiosity:
Why? (and why a colon at the end?)
Select Case Range("N17").Value
Case Range("N17").Value:Why? (and why a colon at the end?)
ASKER
Thank you guys! Got it to work. And Dave, not sure why I have a : at the end..............
B.
B.
I must have missed rorya's post. The colon is probably just a carryover from some other language.
I was just correcting the case statement's syntax as I assumed that Bright would be looking at different values at some point in the future. IT may have been that he wanted to look at Range("N17")'s value and based on results hide certain sheets. The way its currently written of course the case statement is not required:
Alternatively, if Range("N17") has different values and you (Bright) want to act differently, you can use IF then else or a case statement like this:
I was just correcting the case statement's syntax as I assumed that Bright would be looking at different values at some point in the future. IT may have been that he wanted to look at Range("N17")'s value and based on results hide certain sheets. The way its currently written of course the case statement is not required:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shtVisible As String
If Target.Count > 1 Or Target.Value = vbNullString Then Exit Sub
If Not Intersect(Target, Range("N17")) Is Nothing Then 'this detects that a change was made at the identified range
Application.ScreenUpdating = False
shtVisible = "CA_Questionaire,CA_Prioritization,CA_Priority_Heatmap,Maturity Model,Radar Chart,100%Chart,Priorities," & _
"CA_Detailed_Results,CA_Summary_Scores"
On Error Resume Next
Sheets(Split(shtVisible, ",")).Visible = xlSheetVisible
On Error GoTo 0
Application.ScreenUpdating = True
Sheet2.Unprotect Password:="pass"
Worksheets("Question_DB").Range(Replace(Target.Value, " ", "_")).Copy Worksheets("CA_Questionaire").Range("A2")
Application.GoTo Worksheets("CA_Questionaire").Range("A2")
End If
End SubAlternatively, if Range("N17") has different values and you (Bright) want to act differently, you can use IF then else or a case statement like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shtVisible As String
If Target.Count > 1 Or Target.Value = vbNullString Then Exit Sub
If Not Intersect(Target, Range("N17")) Is Nothing Then 'this detects that a change was made at the identified range
Application.ScreenUpdating = False
shtVisible = "CA_Questionaire,CA_Prioritization,CA_Priority_Heatmap,Maturity Model,Radar Chart,100%Chart,Priorities," & _
"CA_Detailed_Results,CA_Summary_Scores"
Select Case Range("N17").Value
Case "ViewAll":
On Error Resume Next
Sheets(Split(shtVisible, ",")).Visible = xlSheetVisible
On Error GoTo 0
Case "ViewOne":
Sheets("TheOneSheet").visible = xlSheetVisible
Case Else:
End Select
Application.ScreenUpdating = True
Sheet2.Unprotect Password:="pass"
Worksheets("Question_DB").Range(Replace(Target.Value, " ", "_")).Copy Worksheets("CA_Questionaire").Range("A2")
Application.GoTo Worksheets("CA_Questionaire").Range("A2")
End If
End Sub
Perhaps you could post the code that contains the Hide process - use the code style in the posting window as it makes it easier to read.