Forum Discussion

LaMamaDePetra's avatar
LaMamaDePetra
Copper Contributor
May 24, 2022

Hide and Unhide Rows Based on Cell Value

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. 
Excel
Excel on Mac
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 24, 2022

    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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 24, 2022

    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
  • kthony123's avatar
    kthony123
    Copper Contributor
    Jan 02, 2023

    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.

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jan 03, 2023

      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.