Hide and Unhide Rows Based on Cell Value

[LaMamaDePetra]

Hello everyone hope you are doing good. 

Im in need of help with a form im doing on excel, i need a code to make rows hide and unhide based on cell value from a data validation list. Ive found some online but they dont work. My understanding is that the code needs to go on the sheet tab. 

Ive locked all the cells except the ones that can be filled so the code needs to work with the sheet protected idk if that is an issue. 

I would like for the first 5 rows of the form to remain always unhidden since its the header and everything after that to be hidden if a value is not yet selected from the drop down menu. 

Also here are some of the codes i found. 

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Activate

If Not Application.Intersect (Range(“J5"), Range(Target.Address) ) Is Nothing Then

Select Case Target.Value

Case Is = “US$ USD”:  Rows(“34:61”).EntireRow.Hidden = True

Rows(“6:33”).EntireRow.Hidden = False

Case Is = “RD$ DOP”:  Rows(“6:33”).EntireRow.Hidden = False

Rows(“34:61”).EntireRow.Hidden = True

End Select

End If

End Sub

‘sheet is now protected

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range(“J5”).Value = “US$ USD” Then

        Rows(“34:61”).EntireRow.Hidden = True

Rows(“6:33”).EntireRow.Hidden = False

    ElseIf Range(“J5”).Value = “RD$ DOP” Then

        Rows(“6:33”).EntireRow.Hidden = True

Rows(“34:61”).EntireRow.Hidden = False

    End If 

End Sub 

Im attaching screenshots of what i need cause i may not be using the correct wording to describe how i want it to look. 

Thank u in advance. 

[#2 · Accepted solution]

@LaMamaDePetra

The code belongs in the worksheet module; you can activate this by right-clicking the sheet tab and selecting 'View Code' from the context menu.

VBA requires straight quotes " around text strings, not curly quotes “.

Try this version of the code. Change "secret" to the password that you used to protect the sheet (use "" if none).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("J5"), Target) Is Nothing Then
        Application.EnableEvents = False
        Me.Unprotect Password:="secret"
        Range("A6:A61").EntireRow.Hidden = True
        Select Case Range("J5").Value
            Case "US$ USD"
                Range("A6:A33").EntireRow.Hidden = False
            Case "RD$ DOP"
                Range("A34:A61").EntireRow.Hidden = False
        End Select
        Me.Protect Password:="secret"
        Application.EnableEvents = True
    End If
End Sub

[#3]

thank you very much it worked perfectly!

[#4]

@LaMamaDePetra 

Hi, for some reason this will not run. I have tried multiple time to hide and unhide based on cell value. Any help would be greatly appreciated!

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C5"), Target) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:="secret"
Range("A6:A22").EntireRow.Hidden = True
Select Case Range("C5").Value
Case "Annual Salary"
Range("A7").EntireRow.Hidden = False
Case "Hourly Wage"
Range("A16").EntireRow.Hidden = False
End Select
Me.Protect Password:="secret"
Application.EnableEvents = True
End If
End Sub
Sub Test(ByVal Target As Range)
If Not Intersect(Range("C5"), Target) Is Nothing Then
Application.EnableEvents = False
Range("6:22").EntireRow.Hidden = True
Select Case Range("c5").Value
Case "Annual Salary"
Range("a7").EntireRow.Hidden = False
Case "Hourly Range"
Range("a16").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End If
End Sub

I am trying to hide/unhide rows based on c5. then based on the result of c5 if c7 unhidden and value select on hide either row c10:c12 or c8:c9.

[#5]

@kthony123 

Did you copy the Worksheet_Change procedure into the worksheet module, i.e. the module that you see if you right-click the sheet tab and select 'View Code' from the context menu? It won't work if it is in another module.