VBA Hide Row if cell = False, Unhide if = True, auto update

[Fridays]

Hi All
I'm a novice and looking for help to fix and resolve this. I thought it would be straight forward but I've been going around in circles to get it to work.
I have sheet1 with check boxes (across columns) that informs sheet2 rows if they are checked (True) or un-checked (False).
On sheet2 I need the rows with a cell value of 'False' to be hidden, and 'True' to be unhidden. This needs to auto update when the cell changes (because a box is checked).
The True/False Data on Sheet2 is in Column H from Row 9:300:

Sub Hide_Rows_Based_On_Cell_Value()

StartRow = 9

EndRow = 300

ColNum = 4

For i = StartRow To EndRow

If Cells(i, ColNum).Value = "FALSE" Then

Cells(i, ColNum).EntireRow.Hidden = True

Else

Cells(i, ColNum).EntireRow.Hidden = False

End If

Next if

End Sub

*I also added a filter and tried to get it to auto update but I couldn't get this to work either.
Thanks in advance

 

[Fridays]

VBA to Update Filter:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

 

[kevin9999]

Can I safely assume that you've linked the checkboxes on sheet 1 to cells in column H on sheet 2? If so, put the following formula anywhere on sheet 2:

=COUNTIF(H9:H300,FALSE)

Then put the following code in the sheet 2 code module (right-click the sheet 2 tab name, select View Code & copy the code into the window that appears on the right of screen).

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 9 To 300
        If Cells(i, 8).Value = False Then Rows(i).Hidden = True Else Rows(i).Hidden = False
    Next i
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Fridays]

Can I safely assume that you've linked the checkboxes on sheet 1 to cells in column H on sheet 2? If so, put the following formula anywhere on sheet 2:

=COUNTIF(H9:H300,FALSE)

Then put the following code in the sheet 2 code module (right-click the sheet 2 tab name, select View Code & copy the code into the window that appears on the right of screen).

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 9 To 300
        If Cells(i, 8).Value = False Then Rows(i).Hidden = True Else Rows(i).Hidden = False
    Next i
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Hi Kevin
Thanks for your reply.
This hasn't worked for me.
It's a bit complicated. The check boxes on Sheet1 are organised into categories across columns so the True/False is based on a Lookup, returning the value to the left (Check box). I was hoping the True False would make running VBA on Sheet2 easier.
I'll try put together and example.

 

[kevin9999]

I look forward to your clarification. Best if you could use the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform.

 

[Fridays]

 

[kevin9999]

OK, so can you explain the connection between the checkboxes on sheet 1 and the rows on sheet 2? An example (before & after) would be ideal.

 

[Fridays]

View attachment 101062

This is not what this sheet is used for but I hope it makes sense of what i'm trying to achieve?
H15:H20 Should be hidden as they were not selected on Sheet1

 

[kevin9999]

I can't see column H in your image. What do you mean by they were 'noi]t selected' on sheet 1?

 

[Fridays]

OK, so can you explain the connection between the checkboxes on sheet 1 and the rows on sheet 2? An example (before & after) would be ideal.

Sorry I didn't realise that would post twice.

On Sheet1 the check boxes are linked to the cell below ie. The 'Cookie' checkbox is 'True' in cell E6.
On Sheet2 the following formula is in D9: =XLOOKUP(F9,Sheet1!$F$6:$F$24,Sheet1!$E$6:$E$24) - this formula has been adjusted for each category ie Biscuits = $F$6:$F$24 etc. Not the most efficient lookup tbh