Rafiello13579
New Member
- Joined
- Mar 3, 2025
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I have a VBA code for hiding/unhiding rows depending on the value of a cell within the sheet where the rows are hidden/unhidden. Below is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "": Rows("17:31").EntireRow.Hidden = True
Case Is = 0: Rows("17:31").EntireRow.Hidden = True
Case Is = 1: Rows("18:31").EntireRow.Hidden = True
Rows("17").EntireRow.Hidden = False
Case Is = 2: Rows("19:31").EntireRow.Hidden = True
Rows("17:18").EntireRow.Hidden = False
Case Is = 3: Rows("20:31").EntireRow.Hidden = True
Rows("17:19").EntireRow.Hidden = False
Case Is = 4: Rows("21:31").EntireRow.Hidden = True
Rows("17:20").EntireRow.Hidden = False
Case Is = 5: Rows("22:31").EntireRow.Hidden = True
Rows("17:21").EntireRow.Hidden = False
Case Is = 6: Rows("23:31").EntireRow.Hidden = True
Rows("17:22").EntireRow.Hidden = False
Case Is = 7: Rows("24:31").EntireRow.Hidden = True
Rows("17:23").EntireRow.Hidden = False
Case Is = 8: Rows("25:31").EntireRow.Hidden = True
Rows("17:24").EntireRow.Hidden = False
Case Is = 9: Rows("26:31").EntireRow.Hidden = True
Rows("17:25").EntireRow.Hidden = False
Case Is = 10: Rows("27:31").EntireRow.Hidden = True
Rows("17:26").EntireRow.Hidden = False
Case Is = 11: Rows("28:31").EntireRow.Hidden = True
Rows("17:27").EntireRow.Hidden = False
Case Is = 12: Rows("29:31").EntireRow.Hidden = True
Rows("17:28").EntireRow.Hidden = False
Case Is = 13: Rows("30:31").EntireRow.Hidden = True
Rows("17:29").EntireRow.Hidden = False
Case Is = 14: Rows("31").EntireRow.Hidden = True
Rows("17:30").EntireRow.Hidden = False
Case Is = 15: Rows("17:31").EntireRow.Hidden = False
End Select
End If
End Sub
The code works fine as is, but I was wondering if there was a way to change the range below in red to another sheet's cell value:
Application.Intersect(Range("B4"), Range(Target.Address))
The idea is to use a different sheet as a "control panel" to change which rows to hide/unhide as needed. I tried the following methods, but neither worked:
Application.Intersect(Sheets("Function Count").Range("B4"), Range(Target.Address))
Application.Intersect(Range(Sheets("Function Count").Range("B4")), Range(Target.Address))
If this is not possible, it's not the end of the world. I can make it work. It would just be convenient.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "": Rows("17:31").EntireRow.Hidden = True
Case Is = 0: Rows("17:31").EntireRow.Hidden = True
Case Is = 1: Rows("18:31").EntireRow.Hidden = True
Rows("17").EntireRow.Hidden = False
Case Is = 2: Rows("19:31").EntireRow.Hidden = True
Rows("17:18").EntireRow.Hidden = False
Case Is = 3: Rows("20:31").EntireRow.Hidden = True
Rows("17:19").EntireRow.Hidden = False
Case Is = 4: Rows("21:31").EntireRow.Hidden = True
Rows("17:20").EntireRow.Hidden = False
Case Is = 5: Rows("22:31").EntireRow.Hidden = True
Rows("17:21").EntireRow.Hidden = False
Case Is = 6: Rows("23:31").EntireRow.Hidden = True
Rows("17:22").EntireRow.Hidden = False
Case Is = 7: Rows("24:31").EntireRow.Hidden = True
Rows("17:23").EntireRow.Hidden = False
Case Is = 8: Rows("25:31").EntireRow.Hidden = True
Rows("17:24").EntireRow.Hidden = False
Case Is = 9: Rows("26:31").EntireRow.Hidden = True
Rows("17:25").EntireRow.Hidden = False
Case Is = 10: Rows("27:31").EntireRow.Hidden = True
Rows("17:26").EntireRow.Hidden = False
Case Is = 11: Rows("28:31").EntireRow.Hidden = True
Rows("17:27").EntireRow.Hidden = False
Case Is = 12: Rows("29:31").EntireRow.Hidden = True
Rows("17:28").EntireRow.Hidden = False
Case Is = 13: Rows("30:31").EntireRow.Hidden = True
Rows("17:29").EntireRow.Hidden = False
Case Is = 14: Rows("31").EntireRow.Hidden = True
Rows("17:30").EntireRow.Hidden = False
Case Is = 15: Rows("17:31").EntireRow.Hidden = False
End Select
End If
End Sub
The code works fine as is, but I was wondering if there was a way to change the range below in red to another sheet's cell value:
Application.Intersect(Range("B4"), Range(Target.Address))
The idea is to use a different sheet as a "control panel" to change which rows to hide/unhide as needed. I tried the following methods, but neither worked:
Application.Intersect(Sheets("Function Count").Range("B4"), Range(Target.Address))
Application.Intersect(Range(Sheets("Function Count").Range("B4")), Range(Target.Address))
If this is not possible, it's not the end of the world. I can make it work. It would just be convenient.