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_]

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

I have just realized that the checkboxes are not visible with the add-in. Therefore, I've taken a print screen:

 

[caet_]

Hello all!
Any ideas?
Thank you

 

[Fluff]

Are your checkboxes AxtiveX or Form Controls?
Also is the A, B etc next to the checkbox the actual caption, or just a value in the cell?

 

[caet_]

Are your checkboxes AxtiveX or Form Controls?
Also is the A, B etc next to the checkbox the actual caption, or just a value in the cell?

Hello!
They are form controls. The A, B, etc are the actual captions
Thank you!

 

[Fluff]

Ok, how about

Sub caet()
   With ActiveSheet.Shapes(Application.Caller)
      Sheets("sheet 2").Rows(.TopLeftCell.Row + 4).Hidden = .ControlFormat.Value <> 1
   End With
End Sub

and assign this to each checkbox

 

[caet_]

Ok, how about

Sub caet()
   With ActiveSheet.Shapes(Application.Caller)
      Sheets("sheet 2").Rows(.TopLeftCell.Row + 4).Hidden = .ControlFormat.Value <> 1
   End With
End Sub

and assign this to each checkbox

Thank you @Fluff, i had the idea that i had already questioned this, but I was not sure and couldn't find it.
When you say to assign it to each checkbox, do you mean to replace ".ControlFormat.Value" by "nameofthecheckbox.Value"?
And should I use this code in module or in the sheet?
Thank you a lot

 

[rollis13]

Hi to all.
I was trying something like this with event Worksheet_Activate instead of Worksheet_Change. Every time you move to sheet Part2 the marked boxes in Part1 will determine the hiding of the rows in Part2:

Private Sub Worksheet_Activate()
    ActiveSheet.Unprotect Password:="123"
    Application.ScreenUpdating = False
    With Sheets("Part2")
        If Sheets("Part1").Range("D2") = True Then .Rows("5").EntireRow.Hidden = True
        If Sheets("Part1").Range("D3") = True Then .Rows("6").EntireRow.Hidden = True
        If Sheets("Part1").Range("D4") = True Then .Rows("7").EntireRow.Hidden = True
        If Sheets("Part1").Range("D5") = True Then .Rows("8").EntireRow.Hidden = True
    End With
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
End Sub

 

[caet_]

Hi to all.
I was trying something like this with event Worksheet_Activate instead of Worksheet_Change. Every time you move to sheet Part2 the marked boxes in Part1 will determine the hiding of the rows in Part2:

Private Sub Worksheet_Activate()
    ActiveSheet.Unprotect Password:="123"
    Application.ScreenUpdating = False
    With Sheets("Part2")
        If Sheets("Part1").Range("D2") = True Then .Rows("5").EntireRow.Hidden = True
        If Sheets("Part1").Range("D3") = True Then .Rows("6").EntireRow.Hidden = True
        If Sheets("Part1").Range("D4") = True Then .Rows("7").EntireRow.Hidden = True
        If Sheets("Part1").Range("D5") = True Then .Rows("8").EntireRow.Hidden = True
    End With
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
End Sub

Hello! Thank you!
I have tried, but it's not working properly
This is the attempt: questionA.xlsm

 

[rollis13]

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 ?