Hide rows in a sheet according to selections on another sheet

[caet_]

Hello!
First of all, wishes of a Happy New Year!

I am new to VBA and I'd like to ask for some help.

In sheet 1, there are several options that one can choose (A, B, C and D). More than option can be choosen.

After that, I would like that in sheet2, only the choices made in sheet1 appear in sheet 2.

For example, let's say I choose A and C on sheet 1. Rows 6 and 8 should be hidden and so on (the row 4 needs to be always visible).

Sheet 1:

question.xlsx
	A	B	C	D	E	F
1
2	A		A	FALSE	0
3	B		B	FALSE	0
4	C		C	FALSE	0
5	D		D	FALSE	0
6
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=+IF(D2=TRUE,1,0)

Sheet 2:

question.xlsx
	A	B	C	D	E	F	G
1
2	Product
3
4				X (Common to all)
5				A
6				B
7				C
8				D
9
Sheet 2

Thank you a lot!

Th

 

[caet_]

Please explain, what does "not working properly" mean ?
As per my testing in your attached attempt you have 4 checked checkboxes so to me this means: non rows showing in Part2.
Manually unhide the rows in Part2, uncheck some checkboxes in Part1 and then go back to Part2. Isn't this what you are asking for ?

Perhaps I haven't explained it right. 4 checked checkboxes would be every row to be shown in Part2.
And I would like the code to work automatically (without needing to manually unhiding).
For exemple, if I check Product A,B,C in Part1; the rows for A,B,C should be showed in Part2. I don't know if it is possible.
Sorry if I was not clear

 

[rollis13]

It's just the other way around, so, reverse the condition when comparing: from True to False, like this:

If Sheets("Part1").Range("D2") = False Then .Rows("5").EntireRow.Hidden = True
If Sheets("Part1").Range("D3") = False Then .Rows("6").EntireRow.Hidden = True
If Sheets("Part1").Range("D4") = False Then .Rows("7").EntireRow.Hidden = True
If Sheets("Part1").Range("D5") = False Then .Rows("8").EntireRow.Hidden = True

So, what happens once the rows are hidden, are they to stay hidden or when you uncheck a checkbox they are to automatically to show again in Part2 ?

 

[caet_]

It's just the other way around, so, reverse the condition when comparing: from True to False, like this:

If Sheets("Part1").Range("D2") = False Then .Rows("5").EntireRow.Hidden = True
If Sheets("Part1").Range("D3") = False Then .Rows("6").EntireRow.Hidden = True
If Sheets("Part1").Range("D4") = False Then .Rows("7").EntireRow.Hidden = True
If Sheets("Part1").Range("D5") = False Then .Rows("8").EntireRow.Hidden = True

So, what happens once the rows are hidden, are they to stay hidden or when you uncheck a checkbox they are to automatically to show again in Part2 ?

When I uncheck a checkbox I would like them to automatically show again in Part 2
What happens now is when I uncheck/check again, it does not update in Part2 (only sometimes). I don't undertstand why.
Most recente attempt: questionA.xlsm

 

[rollis13]

Have a try changing these lines of code like this:

If Sheets("Part1").Range("D2") = False Then .Rows("5").EntireRow.Hidden = True Else .Rows("5").EntireRow.Hidden = False
If Sheets("Part1").Range("D3") = False Then .Rows("6").EntireRow.Hidden = True Else .Rows("6").EntireRow.Hidden = False
If Sheets("Part1").Range("D4") = False Then .Rows("7").EntireRow.Hidden = True Else .Rows("7").EntireRow.Hidden = False
If Sheets("Part1").Range("D5") = False Then .Rows("8").EntireRow.Hidden = True Else .Rows("8").EntireRow.Hidden = False

 

[caet_]

Have a try changing these lines of code like this:

If Sheets("Part1").Range("D2") = False Then .Rows("5").EntireRow.Hidden = True Else .Rows("5").EntireRow.Hidden = False
If Sheets("Part1").Range("D3") = False Then .Rows("6").EntireRow.Hidden = True Else .Rows("6").EntireRow.Hidden = False
If Sheets("Part1").Range("D4") = False Then .Rows("7").EntireRow.Hidden = True Else .Rows("7").EntireRow.Hidden = False
If Sheets("Part1").Range("D5") = False Then .Rows("8").EntireRow.Hidden = True Else .Rows("8").EntireRow.Hidden = False

Works perfectly! ?Thank you so much!

 

[rollis13]

Glad having been of some help .
Now you can cut down this part of the macro like this:

With Sheets("Part2")
    For x = 2 To 5
        If Sheets("Part1").Range("D" & x) = False Then .Rows(x + 3).EntireRow.Hidden = True Else .Rows(x + 3).EntireRow.Hidden = False
    Next x
End With