Hiding a variable amount of rows based on a drop down in a different worksheet

Vonsteiner · Oct 9th 2015

Hello,

I am trying to hide a variable number of rows in 8 different worksheets based on a name selected from a drop down menu in a different worksheet. Each time a new name is selected in the drop down all the rows need to be unhidden then re-hidden based on the new name.

In sheet 1 I have a drop down with roughly 200 names. Each time one is selected I need all the rows NOT associated with this name to become hidden in Sheets 4-12 and only show the rows that include the selected name. If possible I would then like to password protect sheets 4-12 so they cannot be edited or manipulated in any way.

Here is a synopsis of my workbook:
Sheet 1 is the dashboard and contains results that have been compiled in a different worksheet (Sheet 3)
Sheet 2 is very hidden and contains the list of names that appear in the drop down and a number associated to them (some names have multiple numbers and some only have 1)
Sheet 3 is very hidden and contains the compiled data from sheets 4-11 based on name
Sheets 4-12 have the data that is being compiled into sheet 3 and shown on sheet 1. these sheets currently have just the number associated with each name but I can add a column for the name if needed to make this work.
Sheets 4-12 can have from 10-1500 rows of data depending on the month

When presenting the data to the specific name selected in the drop down in sheet 1 I need only the data (rows) within sheets 4-12 that corresponds to this name shown and all the other rows hidden from view.

So far this is the code I have to unhide all the rows. I am unsure of how to call each case in order for the code to hide the necessary rows.

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Index
        Case 4, 5, 6, 7, 8, 9, 10, 11, 12
            If Target.Address(False, False) = "C4" Then
            Application.ScreenUpdating = False
            Worksheet("Sheet4").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheet("Sheet5").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheet("Sheet6").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheet("Sheet7").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheets("Sheet8").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheets("Sheet9").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheets("Sheet10").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheets("Sheet11").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Worksheets("Sheet12").Activate
            Cells.Select
            Rows("3:1500").Hidden = False
            Select Case Target.Value
                Case "A", "B"
                    Rows("25:34").Hidden = True
                    Rows("44:53").Hidden = True
                Case "C", "D"
                    Rows("24:44").Hidden = True
                Case "E"
                    Rows("34:53").Hidden = True
            End Select
            Application.ScreenUpdating = True
            End If
    End Select
End Sub

Display More

Any help would be greatly appreciated.

cytop · Oct 10th 2015

Re: Hiding a variable amount of rows based on a drop down in a different worksheet

Your description does not match the code...

Quote

specific name selected in the drop down in sheet 1

But the code runs on a changes on sheets 4 - 12

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
    Select Case Sh.Index 
    Case 4, 5, 6, 7, 8, 9, 10, 11, 12

Please upload a sample workbook

[sw]*[/sw]

Vonsteiner · Oct 11th 2015

Re: Hiding a variable amount of rows based on a drop down in a different worksheet

cytop, I have added a smaple workbook of my data. The VBA code is within the VQR worksheet.

I didn't realize CASE was calling from those worksheets. The C4 reference drop-down is in the VQR sheet. In that case the area where CASE is should just reference Sheet 1.

cytop · Oct 11th 2015

Re: Hiding a variable amount of rows based on a drop down in a different worksheet

This, in the worksheet class module for the sheet VQR, will hide rows as you requested.

Code

Private Sub WorkSheet_Change(ByVal Target As Range)


   Dim iTemp            As Integer


   On Error GoTo Catch


   If Target.Address <> "$C$4" Then Exit Sub


   With Application
      .Calculation = xlCalculationManual
      .EnableEvents = False
   End With


   For iTemp = 4 To Sheets.Count


      With Sheets(iTemp)
         '.Cells.AutoFilter
         .UsedRange.AutoFilter Field:=2, Criteria1:=Target.Value
      End With
   Next


Catch:


   With Application
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
   End With
   
End Sub

Display More

However, because of the amount of processing, there's a very noticable delay after selecting something from the dropdown.

Because of this, I haven't tackled the password - I think you need to have another look at how this works, even if only to add a button to actually filter the sheets and apply the password when the user is ready to 'lock it down'.

Vonsteiner · Oct 27th 2015

Re: Hiding a variable amount of rows based on a drop down in a different worksheet

Thank you very much cytop. That worked great for what I needed.

Hiding a variable amount of rows based on a drop down in a different worksheet

Participate now!

Share

Users Viewing This Thread