Using a checkbox to unhide rows on another sheet

[briggss1]

I have the need to use a filtering cover sheet to unhide only relevant data entry fields on an adjacent sheet. Ideally I could steer clear of VBA, but if not, oh well. In principle, I am trying to do the following as per the example:

I want to select the following by clicking or adding a value next to the item using a simple alphanumeric value or even a checkbox. This is done on Sheet 1.

Once this happens, on Sheet 2 where all rows will be hidden by default, I want to unfilter (or open up/make visible) all of the rows on that respective category that contain an "x" for that category.

This is the complete table as an example wioth nothing opened or closed.

This is how the table will look with nothing checked on sheet 1 (all hidden), nothing requiring a data entry to be completed.

This is how sheet 2 will look with the items checked as indicated in my first screenshot from Sheet 1.

 

[John_w]

I think VBA is needed for this.

On Sheet1, add a table named "CategoriesSelected" like this:

Category	Selected
Apples	x
Oranges
Peaches	x
Milk	x
Bread
Eggs

Note that the categories are in the first column so the code can do VLOOKUP on them. Using a table, instead of a specific range, means rows can be added/removed without changing the code. The "x" can be any alphanumeric character(s) because the code looks for '<> ""' rather than '= "x"'.

Put this code in the module for Sheet2:

Private Sub Worksheet_Activate()

    Dim table As ListObject
    Dim c As Long, r As Long
    Dim hideRow As Boolean
   
    Set table = ThisWorkbook.Worksheets("Sheet1").ListObjects("CategoriesSelected")

    Application.ScreenUpdating = False
   
    With Me
        .Rows.EntireRow.Hidden = False
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            hideRow = True
            For c = 5 To .Cells(1, .Columns.Count).End(xlToLeft).Column
                If Not IsEmpty(.Cells(r, c).Value) And Application.WorksheetFunction.VLookup(.Cells(1, c).Value, table.Range, 2, False) <> "" Then
                    hideRow = False
                End If
            Next
            If hideRow Then .Rows(r).EntireRow.Hidden = True
        Next
    End With

    Application.ScreenUpdating = True
   
End Sub