Aug 27 2020 12:38 AM
Hi,
I am working on a document where based on the selection in a cell ("$F$6") of No, EWP or AusPost different rows in corresponding worksheets hide/unhide.
The issue I am having is:
if I select "No" in ("$F$6") it hides everything I require hidden (no issue)
if I select either "EWP" or "AusPost" it unhides the correct rows (no issue)
if I select either "EWP" or "AusPost" after having already selected the opposite value "EWP" or "AusPost" it will unhide the correct rows but not rehide the others.
see code and screenshot of code.
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Worksheets("Contractors").Range("15:16,39:44").EntireRow.Hidden = True
Case Is = "EWP": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
Case Is = "EWP": Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Worksheets("Consultants").Range("18:21:22:24").EntireRow.Hidden = True
Case Is = "EWP": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
Case Is = "EWP": Worksheets("Consultants").Range("22:24").EntireRow.Hidden = True
Case Is = "AusPost": Worksheets("Consultants").Range("22:24").EntireRow.Hidden = False
Case Is = "AusPost": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True
End Select
End If
End Sub
' Contractors Worksheet - 42:44 = AusPost specific Lines
' Consultants Worksheet - 22:24 = AusPost specific Lines
' Contractors Worksheet - 15,39:41 = EWP specific Lines
' Consultants Worksheet - 18:21 = EWP specific Lines
Aug 27 2020 12:55 AM
This was the original code in the document when it was a "yes", "no" or "blank" dropdown list and it would correctly unhide/hide the rows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Case Is = "Yes": Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True
Case Is = "Yes": Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
End Select
End If
End Sub
Aug 27 2020 04:26 AM - edited Aug 27 2020 04:30 AM
@jacksonattwood Combine all the hide and unhide actions into one Case statement, like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("$F$6"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No":
Worksheets("Contractors").Range("15:16,39:44").EntireRow.Hidden = True
Worksheets("Consultants").Range("18:24").EntireRow.Hidden = True
Case Is = "EWP":
Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = False
Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = True
Worksheets("Consultants").Range("18:21").EntireRow.Hidden = False
Worksheets("Consultants").Range("22:24").EntireRow.Hidden = True
Case Is = "AusPost":
Worksheets("Contractors").Range("15:15,39:41").EntireRow.Hidden = True
Worksheets("Contractors").Range("16:16,42:44").EntireRow.Hidden = False
Worksheets("Consultants").Range("22:24").EntireRow.Hidden = False
Worksheets("Consultants").Range("18:21").EntireRow.Hidden = True
End Select
End If
End Sub
Tested it and it seems to work as intended.