ChristinaL
05-03-2020, 06:19 PM
Hi,
I'm new to VBA codes (first macro ever) and having been trying to automate Private Sub codes so that when I make one cell change (drop down selection) from the Summary worksheet it auto hides rows accordingly across various sheets.
My goal is to hide non-relevant cases/information throughout a Financial model Workbook so that it is simple for an investor to review. My struggle is that every worksheet needs different rows hidden.
These are two examples of my code on different worksheets, which is functioning as a Private Sub but I have to double click (open to edit then close) the reference cell (referring back to the Summary worksheet) to get the macro to run on that page, which is not feasible for the investors to do:
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("11:199").EntireRow.Hidden = False
Rows("200:574").EntireRow.Hidden = True
Case Is = "Worst": Rows("388:574").EntireRow.Hidden = False
Rows("11:387").EntireRow.Hidden = True
Case Is = "Best": Rows("200:387").EntireRow.Hidden = False
Rows("11:199").EntireRow.Hidden = True
Rows("388:574").EntireRow.Hidden = True
Case Is = "ALL": Rows("11:574").EntireRow.Hidden = False
End Select
End If
End Sub
------------------
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("66:223").EntireRow.Hidden = False
Rows("224:537").EntireRow.Hidden = True
Case Is = "Worst": Rows("381:537").EntireRow.Hidden = False
Rows("66:380").EntireRow.Hidden = True
Case Is = "Best": Rows("224:380").EntireRow.Hidden = False
Rows("66:223").EntireRow.Hidden = True
Rows("381:537").EntireRow.Hidden = True
Case Is = "ALL": Rows("66:537").EntireRow.Hidden = False
End Select
End If
End Sub
-----------------
Thanks in advance for anyone who could point me in the right direction. At the moment I am overwhelmed with so much new information in these forums.
I'm new to VBA codes (first macro ever) and having been trying to automate Private Sub codes so that when I make one cell change (drop down selection) from the Summary worksheet it auto hides rows accordingly across various sheets.
My goal is to hide non-relevant cases/information throughout a Financial model Workbook so that it is simple for an investor to review. My struggle is that every worksheet needs different rows hidden.
These are two examples of my code on different worksheets, which is functioning as a Private Sub but I have to double click (open to edit then close) the reference cell (referring back to the Summary worksheet) to get the macro to run on that page, which is not feasible for the investors to do:
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("11:199").EntireRow.Hidden = False
Rows("200:574").EntireRow.Hidden = True
Case Is = "Worst": Rows("388:574").EntireRow.Hidden = False
Rows("11:387").EntireRow.Hidden = True
Case Is = "Best": Rows("200:387").EntireRow.Hidden = False
Rows("11:199").EntireRow.Hidden = True
Rows("388:574").EntireRow.Hidden = True
Case Is = "ALL": Rows("11:574").EntireRow.Hidden = False
End Select
End If
End Sub
------------------
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("66:223").EntireRow.Hidden = False
Rows("224:537").EntireRow.Hidden = True
Case Is = "Worst": Rows("381:537").EntireRow.Hidden = False
Rows("66:380").EntireRow.Hidden = True
Case Is = "Best": Rows("224:380").EntireRow.Hidden = False
Rows("66:223").EntireRow.Hidden = True
Rows("381:537").EntireRow.Hidden = True
Case Is = "ALL": Rows("66:537").EntireRow.Hidden = False
End Select
End If
End Sub
-----------------
Thanks in advance for anyone who could point me in the right direction. At the moment I am overwhelmed with so much new information in these forums.