hwbell3
05-05-2020, 10:05 AM
I have created the following VBA code that works well to hide and unhide rows in a given sheet. I use this basic code in a number of sheets where the "University-wide" and "College by College" Cases are used and selected from a drop box. My challenge is I want to be able to set the value of "L8" in sheet 1 and use it in all of the other sheets where this code appears instead of having to select the value in each sheet where the code is used.
How do I do this?
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("L8"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "University-wide": Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College": Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select
End If
End Sub
Paul_Hossler
05-05-2020, 12:44 PM
Not tested, but the For Each ws ..... loop goes through all the worksheets
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Application.Intersect(Range("L8"), Target) Is Nothing Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Select Case Target.Value
Case Is = "University-wide"
ws.Rows("48:216").Hidden = True
ws.Rows("10:47,217:218").Hidden = False
Case Is = "College by College"
ws.Rows("10:30,47:220").Hidden = False
ws.Rows("31:46").Hidden = True
End Select
Next
End Sub
hwbell3
05-06-2020, 10:25 AM
Paul,
Thanks for the Code. Initially I omitted the "Option Explicit" and got error messages. When I added the "Option Explicit" the error messages disappeared but the code did not work. Any thoughts on what might be keeping the code from working?
hwbell3
paulked
05-06-2020, 01:29 PM
Do you want to run this on every sheet in the workbook or just a selection? If a selection, which sheets? Or post your workbook (remove sensitive data if you need to).
hwbell3
05-06-2020, 02:19 PM
I want to run it on a selection of sheets. More specifically, I want to use the "University-wide" and "College by College" values to hide and unhide rows or columns on Sheets 2, 3, 6, 8, 12, 13, 26, 27, 29, 40, and 41. Part of the challenge is that the rows and columns hidden and unhidden vary from sheet to sheet. Hence, I need to be able to use the "University-wide" and "College by College" values on each of the identified sheets to hide a unique set of rows or columns on each of the identified sheets. I am, therefore, using a different code for each worksheet. Currently I have written a custom code for each sheet that works for each sheet. The code I showed as an example was the code for sheet6. But I don't want to force a user to set the value of "University-wide" or "College by College" 11 different times. Hence, my challenge is to be able to have a user set the value as either "University-wide" or "College by College" in one of the worksheets and have that value activate the code for each of the other sheets where it is used.
paulked
05-06-2020, 02:50 PM
In that case I would suggest that if cell L8 changes on any of those sheets, update cell L8 on all the ones that you want to update. Then in each sheet code module show/hide the rows you want on a Sheet_Activate event. When the sheet is selected the rows will be updated. Example:
Option Explicit
Private Sub Worksheet_Activate()
Select Case Range("L8")
Case Is = "University-wide"
Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College"
Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, str As String
If Application.Intersect(Range("L8"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
str = Range("L8")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet2" Or ws.Name = "Sheet3" Or ws.Name = "Sheet6" Or _
ws.Name = "Sheet8" Or ws.Name = "Sheet12" Or ws.Name = "Sheet13" Or _
ws.Name = "Sheet26" Or ws.Name = "Sheet27" Or ws.Name = "Sheet29" Or _
ws.Name = "Sheet29" Or ws.Name = "Sheet40" Or ws.Name = "Sheet41" Then
ws.Range("L8") = str
End If
Next
Application.EnableEvents = True
End Sub
Paul_Hossler
05-06-2020, 04:07 PM
The code I showed as an example was the code for sheet6
Q1. So this
If Not Application.Intersect(Range("L8"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "University-wide": Range("48:216").EntireRow.Hidden = True
Range("10:47,217:218").EntireRow.Hidden = False
Case Is = "College by College": Range("10:30,47:220").EntireRow.Hidden = False
Range("31:46").EntireRow.Hidden = True
End Select End If
was the rows to hide/unhide on Sheet6, but Sheet 2 has a different group of rows?
Q2. Does each sheet key off of the L8 on THAT sheet? I.e. Sheet2 L8 = University-wide and hides/unhides it's set of rows, and on Sheet6 L8 = College by College which hides that sheet's rows?
Q3. Have you thought about naming the worksheets something more meaningful that "Sheet2", say "North East Schools"?
Q4. Is there a 'signature' on the affected sheets that indicates that the sheets is 'on the list'? Like maybe if B2 has "Courses" then use the L8 logic, otherwise don't on that sheet?
Q5. Similarly, are there markers that could be used to determine dynamically the show/hide ranges?
Q6. Last one --- can you attach a sample workbook?
Maybe in the ThisWorkbook module
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("L8")) Is Nothing Then Exit Sub
If Sh.Range("L8").Value <> "University-wide" And Sh.Range("L8").Value <> "College by College" Then Exit Sub
Application.EnableEvents = False
'only sheet 6 is really correct (probably). Rest need to be made specific
Call ShowHide("Sheet2", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet3", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet6", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet8", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet12", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet13", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet26", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet27", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet29", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet40", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Call ShowHide("Sheet41", Sh.Range("L8"), "48:216", "10:47,217:218", "10:30,47:220", "31:46")
Application.EnableEvents = True
End Sub
Private Sub ShowHide(ws As String, UorC As String, Uhide As String, Ushow As String, Chide As String, Cshow As String)
With Worksheets(ws)
.Range("L8").Value = UorC
Select Case UorC
Case Is = "University-wide"
.Rows(Uhide).Hidden = True
.Rows(Ushow).Hidden = False
Case Is = "College by College"
.Rows(Cshow).Hidden = False
.Range(Chide).Hidden = True
End Select
End With
End Sub
Paul_Hossler
05-06-2020, 04:35 PM
Paul,
Thanks for the Code. Initially I omitted the "Option Explicit" and got error messages. When I added the "Option Explicit" the error messages disappeared but the code did not work. Any thoughts on what might be keeping the code from working?
hwbell3
Option Explicit says that all variables have to be explicitly Dim-ed
I don't see how omitting that would cause errors, that went away when you added it
"Any thoughts" ... No, not without my crystal ball
hwbell3
05-07-2020, 10:41 AM
Paulked,
I tried the code you suggested. When I ran it I got not error messages but I also did not get it to work.
Hwbell3
paulked
05-07-2020, 11:03 AM
Post your workbook or try Paul H's latest code
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.