VBA Help using value in sheet 1 to hide unhide row in sheet 2

[hwbell3]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Post your workbook or try Paul H's latest code