Forum Discussion
LaMamaDePetra
May 24, 2022Copper Contributor
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 ...
- May 24, 2022
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
May 24, 2022MVP
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
- LaMamaDePetraMay 24, 2022Copper Contributorthank you very much it worked perfectly!