Please help with VBA code with hide & unhide different rows

[Cerber1983]

I have a workbook with 2 tabs where:

Tab 1 (General Inputs): Is input tab with List dropdown for one of the fields
Tab 2: Is output tab that is formatted as document
Tab 1 have a cell with drop-down options to choose from:

Apples
Oranges
Tomatoes
Apples & Oranges
Apples & Tomatoes
Oranges & Tomatoes
Apples, Oranges, & Tomatoes

Tab 2 is a document with formatted data pages as a document for each output in Tab 1 (General Inputs):

Rows 404:454 for Apples
Rows 455:505 for Oranges
Rows 506:556 for Tomatoes
Rows 557:607 for Apples & Oranges
Rows 608:658 for Apples & Tomatoes
Rows 659:709 for Oranges & Tomatoes
Rows 353:403 for Apples, Oranges, & Tomatoes

I am trying to write VBA code that will be displaying only pages on Tab 2 based on drop-down value in a cell on Tab 1 (General Inputs)

Below is a code but it doesn't seems to be working
FYI: "Document" Tab is Tab 2 where I need to hide rows based on selection & D162 is a cell on same Tab 2 that is with formula = value of drop-down cell from Tab 1 (General Inputs)

Please help!

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Document")


If Target.Address = "$D$162" Then
If Target.Value = "Apple" Then Rows("353:403").EntireRow.Hidden = True
If Target.Value = "Apple" Then Rows("455:709").EntireRow.Hidden = True
If Target.Value = "Apple" Then Rows("404:454").EntireRow.Hidden = False
If Target.Value = "Orange" Then Rows("353:454").EntireRow.Hidden = True
If Target.Value = "Orange" Then Rows("506:709").EntireRow.Hidden = True
If Target.Value = "Orange" Then Rows("455:505").EntireRow.Hidden = False
If Target.Value = "Tomato" Then Rows("353:505").EntireRow.Hidden = True
If Target.Value = "Tomato" Then Rows("557:709").EntireRow.Hidden = True
If Target.Value = "Tomato" Then Rows("506:556").EntireRow.Hidden = False
If Target.Value = "Apple & Orange" Then Rows("353:556").EntireRow.Hidden = True
If Target.Value = "Apple & Orange" Then Rows("608:709").EntireRow.Hidden = True
If Target.Value = "Apple & Orange" Then Rows("557:607").EntireRow.Hidden = False
If Target.Value = "Apple & Tomato" Then Rows("353:607").EntireRow.Hidden = True
If Target.Value = "Apple & Tomato" Then Rows("659:709").EntireRow.Hidden = True
If Target.Value = "Apple & Tomato" Then Rows("608:658").EntireRow.Hidden = False
If Target.Value = "Orange & Tomato" Then Rows("353:658").EntireRow.Hidden = True
If Target.Value = "Orange & Tomato" Then Rows("659:709").EntireRow.Hidden = False
If Target.Value = "Apple, Orange, & Tomato" Then Rows("404:709").EntireRow.Hidden = True
If Target.Value = "Gold, Platinum, & Diamond" Then Rows("353:403").EntireRow.Hidden = False
End If
End With
End Sub

 

[Osvaldo Palmeiro]

Try a copy of the code below into sheets Document module.

Private Sub Worksheet_Calculate()
 Dim str As String
  Rows("353:709").Hidden = True
  Select Case Range("D162").Value
   Case "Apple": str = "404:454"
   Case "Orange": str = "455:505"
   Case "Tomato": str = "506:556"
   Case "Apple & Orange": str = "557:607"
   Case "Apple & Tomato": str = "608:658"
   Case "Orange & Tomato": str = "659:709"
   Case "Apple, Orange, & Tomato", "Gold, Platinum, & Diamond": str = "353:403"
  End Select
  Rows(str).Hidden = False
End Sub

 

[Cerber1983]

Thank you Osvaldo!

After putting using above suggested code, I am getting following debug error

Also, content in both tabs look like below

 

[Osvaldo Palmeiro]

After putting using above suggested code, I am getting following debug error

Is the sheet Document protected?

Also, content in both tabs look like below

Sorry, but I can't think anything about this issue.

 

[Cerber1983]

Is the sheet Document protected?


Sorry, but I can't think anything about this issue.

No, it is not protected

 

[Akuini]

After putting using above suggested code, I am getting following debug error

Let's try something simple first.
Does it work if you just hide the rows?

Sub try()
Sheets("Document").Rows("353:709").Hidden = True
End Sub

 

[Cerber1983]

Let's try something simple first.
Does it work if you just hide the rows?

Sub try()
Sheets("Document").Rows("353:709").Hidden = True
End Sub

Good morning Akuini!

It worked and rows 353:709 were hidden.

 

[Akuini]

It worked and rows 353:709 were hidden.

You should put Sub Worksheet_Change in sheet "General Inputs", and the target range is B18?
Like this (to hide the rows, I'm using the shorter code by Osvaldo Palmeiro in post #2):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) = "B18" Then
        
        Application.EnableEvents = False
        Sheets("Document").Rows("353:709").Hidden = True
            Select Case Target.Value
                Case "Apple": Str = "404:454"
                Case "Orange": Str = "455:505"
                Case "Tomato": Str = "506:556"
                Case "Apple & Orange": Str = "557:607"
                Case "Apple & Tomato": Str = "608:658"
                Case "Orange & Tomato": Str = "659:709"
                Case "Apple, Orange, & Tomato", "Gold, Platinum, & Diamond": Str = "353:403"
            End Select
        Sheets("Document").Rows(Str).Hidden = False
        
        Application.EnableEvents = True
        
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

 

[Cerber1983]

You should put Sub Worksheet_Change in sheet "General Inputs", and the target range is B18?
Like this (to hide the rows, I'm using the shorter code by Osvaldo Palmeiro in post #2):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) = "B18" Then
       
        Application.EnableEvents = False
        Sheets("Document").Rows("353:709").Hidden = True
            Select Case Target.Value
                Case "Apple": Str = "404:454"
                Case "Orange": Str = "455:505"
                Case "Tomato": Str = "506:556"
                Case "Apple & Orange": Str = "557:607"
                Case "Apple & Tomato": Str = "608:658"
                Case "Orange & Tomato": Str = "659:709"
                Case "Apple, Orange, & Tomato", "Gold, Platinum, & Diamond": Str = "353:403"
            End Select
        Sheets("Document").Rows(Str).Hidden = False
       
        Application.EnableEvents = True
       
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

Thank you Akuini!

It didn't do anything (didn't hide rows). Maybe it will be better explanation:

Sheet 1 is "General Inputs" with Drop Down List in A18 which is also will be displayed in "Document" tab:

A18 is also displayed in cell D162 of "Document" tab where I think either to hide rows in "Document" tab based either on cell B18 of "General Inputs" sheet or D162 "Document" tab

 

[Akuini]

Sheet 1 is "General Inputs" with Drop Down List in A18 which is also will be displayed in "Document" tab:

The dropdown is in A18 or B18?
How did you "Orange" in B18? by dropdown?