SOLVED

Hide and Unhide Rows Based on Cell Value

%3CLINGO-SUB%20id%3D%22lingo-sub-3423222%22%20slang%3D%22en-US%22%3EHide%20and%20Unhide%20Rows%20Based%20on%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%20everyone%20hope%20you%20are%20doing%20good.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3EIm%20in%20need%20of%20help%20with%20a%20form%20im%20doing%20on%20excel%2C%20i%20need%20a%20code%20to%20make%20rows%20hide%20and%20unhide%20based%20on%20cell%20value%20from%20a%20data%20validation%20list.%20Ive%20found%20some%20online%20but%20they%20dont%20work.%20My%20understanding%20is%20that%20the%20code%20needs%20to%20go%20on%20the%20sheet%20tab.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EIve%20locked%20all%20the%20cells%20except%20the%20ones%20that%20can%20be%20filled%20so%20the%20code%20needs%20to%20work%20with%20the%20sheet%20protected%20idk%20if%20that%20is%20an%20issue.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EI%20would%20like%20for%20the%20first%205%20rows%20of%20the%20form%20to%20remain%20always%20unhidden%20since%20its%20the%20header%20and%20everything%20after%20that%20to%20be%20hidden%20if%20a%20value%20is%20not%20yet%20selected%20from%20the%20drop%20down%20menu.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EAlso%20here%20are%20some%20of%20the%20codes%20i%20found.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FDIV%3E%3CDIV%3EActiveSheet.Activate%3C%2FDIV%3E%3CDIV%3EIf%20Not%20Application.Intersect%20(Range(%E2%80%9CJ5%22)%2C%20Range(Target.Address)%20)%20Is%20Nothing%20Then%3C%2FDIV%3E%3CDIV%3ESelect%20Case%20Target.Value%3C%2FDIV%3E%3CDIV%3ECase%20Is%20%3D%20%E2%80%9CUS%24%20USD%E2%80%9D%3A%26nbsp%3B%26nbsp%3BRows(%E2%80%9C34%3A61%E2%80%9D).EntireRow.Hidden%20%3D%20True%3C%2FDIV%3E%3CDIV%3ERows(%E2%80%9C6%3A33%E2%80%9D).EntireRow.Hidden%20%3D%20False%3C%2FDIV%3E%3CDIV%3ECase%20Is%20%3D%20%E2%80%9CRD%24%20DOP%E2%80%9D%3A%26nbsp%3B%26nbsp%3BRows(%E2%80%9C6%3A33%E2%80%9D).EntireRow.Hidden%20%3D%20False%3C%2FDIV%3E%3CDIV%3ERows(%E2%80%9C34%3A61%E2%80%9D).EntireRow.Hidden%20%3D%20True%3C%2FDIV%3E%3CDIV%3EEnd%20Select%3C%2FDIV%3E%3CDIV%3EEnd%20If%3C%2FDIV%3E%3CDIV%3EEnd%20Sub%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%E2%80%98sheet%20is%20now%20protected%3C%2FDIV%3E%3CDIV%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20If%20Range(%E2%80%9CJ5%E2%80%9D).Value%20%3D%20%E2%80%9CUS%24%20USD%E2%80%9D%20Then%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Rows(%E2%80%9C34%3A61%E2%80%9D).EntireRow.Hidden%20%3D%20True%3C%2FDIV%3E%3CDIV%3ERows(%E2%80%9C6%3A33%E2%80%9D).EntireRow.Hidden%20%3D%20False%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20ElseIf%20Range(%E2%80%9CJ5%E2%80%9D).Value%20%3D%20%E2%80%9CRD%24%20DOP%E2%80%9D%20Then%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Rows(%E2%80%9C6%3A33%E2%80%9D).EntireRow.Hidden%20%3D%20True%3C%2FDIV%3E%3CDIV%3ERows(%E2%80%9C34%3A61%E2%80%9D).EntireRow.Hidden%20%3D%20False%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20End%20If%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EEnd%20Sub%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EIm%20attaching%20screenshots%20of%20what%20i%20need%20cause%20i%20may%20not%20be%20using%20the%20correct%20wording%20to%20describe%20how%20i%20want%20it%20to%20look.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202022-05-24%20at%2012.23.20%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374764i5AF639B7C02323A4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-05-24%20at%2012.23.20%20PM.png%22%20alt%3D%22Screen%20Shot%202022-05-24%20at%2012.23.20%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EThank%20u%20in%20advance.%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3423222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423358%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20and%20Unhide%20Rows%20Based%20on%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400222%22%20target%3D%22_blank%22%3E%40LaMamaDePetra%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThe%20code%20belongs%20in%20the%20worksheet%20module%3B%20you%20can%20activate%20this%20by%20right-clicking%20the%20sheet%20tab%20and%20selecting%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3EVBA%20requires%20straight%20quotes%20%22%20around%20text%20strings%2C%20not%20curly%20quotes%20%E2%80%9C.%3C%2FP%3E%0A%3CP%3ETry%20this%20version%20of%20the%20code.%20Change%20%22secret%22%20to%20the%20password%20that%20you%20used%20to%20protect%20the%20sheet%20(use%20%22%22%20if%20none).%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Not%20Intersect(Range(%22J5%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20Me.Unprotect%20Password%3A%3D%22secret%22%0A%20%20%20%20%20%20%20%20Range(%22A6%3AA61%22).EntireRow.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20Select%20Case%20Range(%22J5%22).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22US%24%20USD%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A6%3AA33%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22RD%24%20DOP%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A34%3AA61%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20%20%20%20%20Me.Protect%20Password%3A%3D%22secret%22%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423591%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20and%20Unhide%20Rows%20Based%20on%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423591%22%20slang%3D%22en-US%22%3Ethank%20you%20very%20much%20it%20worked%20perfectly!%3C%2FLINGO-BODY%3E
New Contributor

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. 
 
Screen Shot 2022-05-24 at 12.23.20 PM.png
 
 
Thank u in advance. 
2 Replies
best response confirmed by LaMamaDePetra (New Contributor)
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
thank you very much it worked perfectly!