May 24 2022 12:01 PM
Hello everyone hope you are doing good.
May 24 2022 12:33 PM
SolutionThe 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
May 24 2022 01:13 PM
Jan 02 2023 03:27 PM
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.
Jan 03 2023 03:32 AM
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.
May 24 2022 12:33 PM
SolutionThe 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