Show/Hide Sheets in VBA

[justanotheruser]

Hi all,

This will be a simple fix for you clever VBA people out there. The highlighted code below basically means that if you select any option from a drop down list (E4 is a drop down list) other than Select Entity - the first option, then the Other and BL_PS worksheets will be opened.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
ActiveWorkbook.Unprotect
If Target.Address(False, False) = "E4" Then
    If Target.Value = "Select Entity" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    Sheets("Other").Visible = False
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
  [COLOR="Red"]  If Target.Value <> "Select Entity" Then Sheets("Other").Visible = True And Sheets("BL_PS").Visible = True[/COLOR]
        Application.ScreenUpdating = True
End If
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Unfortunately, this doesn't work and it will not show either of the sheets on any of the selections including select entity - if I put an "Or" operator instead of "And" on that line, then it will work for just other - I'm going to have multiple about 20 sheets that need to be visible if anything other than select entity is chosen - so I would really appreciate a fix!

Thanks in advance for your help

 

[AlphaFrog]

I'm not sure what end result you want but when you select "Select Entity", the blue part or your code runs and you exit the procedure. The red part of the code will never have a chance to run when you select "Select Entity".

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
ActiveWorkbook.Unprotect
If Target.Address(False, False) = "E4" Then
    [COLOR="Blue"]If Target.Value = "Select Entity" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
        Next i
        [COLOR="Sienna"]Exit Sub[/COLOR]
    Sheets("Other").Visible = False
    End If[/COLOR]
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    [COLOR="Red"]If Target.Value <> "Select Entity" Then Sheets("Other").Visible = True And Sheets("BL_PS").Visible = True[/COLOR]
        Application.ScreenUpdating = True
End If
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

 

[justanotheruser]

Hi, that's fine, "Select Entity" is one option from a drop down list. I want to have is so that if Select Entity is chosen, all the other sheets in the workbook are hidden - so I don't know if the blue code is programmed to do that.

Then if you select any other option from the drop down list, i.e. not Select Entity, I want the Other sheet and the BL_PS sheet to be shown. Thanks

 

[justanotheruser]

In fact, no worries, I managed to fix that one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
ActiveWorkbook.Unprotect
If Target.Address(False, False) = "E4" Then
    If Target.Value = "Select Entity" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
        Next i
        Exit Sub
        End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
            Application.ScreenUpdating = True
        ' Used to show non-BU sheets for all entities, no matter of choice from dropdown box. Set all non-BU sheets to true to show them.
        Sheets("Other").Visible = True
        Sheets("BL_PS").Visible = True
End If
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

 

[AlphaFrog]

In your newest code if you select "Select Entity", the workbook is not reprotected because you use Exit Sub

This is what I came up with (not tested).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LR As Long, i As Long
    
    If Target.Address(False, False) = "E4" Then
        ActiveWorkbook.Unprotect
        Application.ScreenUpdating = False
        
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" Then Sheets(i).Visible = False
        Next i
        
        If Target.Value <> "Select Entity" Then
            With Sheets("Mapping")
                LR = .Range("C" & Rows.Count).End(xlUp).Row
                For i = 3 To LR
                    If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then
                        Sheets(.Range("D" & i).Value).Visible = True
                    End If
                Next i
            End With
            Sheets("Other").Visible = True
            Sheets("BL_PS").Visible = True
        End If
        
        Application.ScreenUpdating = True
        ActiveWorkbook.Protect Structure:=True, Windows:=False
    End If
    
End Sub

 

[justanotheruser]

Great thanks, it seems to work, but if it breaks I know where to return to!