Scrolldown menu of Sheet names to Hide/Unhide them

[tkuzminaa]

Hi! I am pretty new to vba. Can you please help me, I need a vba code to create scrolldown menu with Sheet names of worksheets in the document, so that when you select the certain sheet it would make sheets Very Hidden (if they are not already Hidden) and unhide sheets if they are Very Hidden. Is it possible?

Thank you in advance!

 

[mumps]

Place this macro in a regular module and run it once to create a drop down list of sheet names in cell A1. You can change cell reference in the code to place the drop down in any cell you want. If you add sheets or delete sheets, run this macro again to update the drop down list.

Sub SheetDropDown()
    Application.ScreenUpdating = False
    Dim sheetArray As Variant
    ReDim sheetArray(1 To Sheets.Count)
    Dim i As Long
    For i = 1 To Sheets.Count
        sheetArray(i) = ThisWorkbook.Sheets(i).Name
    Next
    With ActiveSheet.Cells(1, 1).Validation 'change cell reference to suit your needs
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(sheetArray, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Application.ScreenUpdating = True
End Sub

Next copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet that contains the drop down list and click 'View Code'. Paste the following macro into the empty code window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub 'change the range to suit your needs
    Application.ScreenUpdating = False
    If ActiveSheet.Name <> Target.Value And Sheets(Target.Value).Visible = xlVeryHidden Then
        Sheets(Target.Value).Visible = True
    ElseIf ActiveSheet.Name <> Target.Value And Sheets(Target.Value).Visible = True Then
        Sheets(Target.Value).Visible = xlVeryHidden
    End If
End Sub

Change the range where indicated in the code to match the cell reference of the cell containing the drop down. Close the code window to return to your sheet. Make your selection in the drop down list.

 

[GTO]

Greetings,

For some reason I was thinking a userform, so just in case:

A userform with:
Two labels, named lblHidden and lblVisible
Two listboxes named lstHidden and lstVisible
Two command buttons named cmdHide and cmdShow

The following in the UserForm's Module:

Option Explicit
  
Dim lVisibleCount As Long
  
Private Sub HideSheet()
Dim Index             As Long
Dim Index2            As Long
Dim bolProcessing     As Boolean
Dim bolLooking2Insert As Boolean
  
AbandonShip:
  If lVisibleCount = 1 Then
    Me.Hide
    DoEvents
    MsgBox "You must leave at least one visible sheet.", vbInformation Or vbOKOnly, "ERROR:"
    Me.Show vbModal
    Exit Sub
  End If
  
  Do
    
    bolProcessing = False
    
    For Index = 0 To (Me.lstVisible.ListCount - 1)
      If Me.lstVisible.Selected(Index) Then
        
        ThisWorkbook.Worksheets(Me.lstVisible.List(Index, 0)).Visible = xlSheetVeryHidden
        bolLooking2Insert = True
        
        For Index2 = 0 To (Me.lstHidden.ListCount - 1)
          If ThisWorkbook.Worksheets(Me.lstHidden.List(Index2, 0)).Index > ThisWorkbook.Worksheets(Me.lstVisible.List(Index, 0)).Index Then
            Me.lstHidden.AddItem Me.lstVisible.List(Index, 0), Index2
            bolLooking2Insert = False
            Exit For
          End If
        Next
        If bolLooking2Insert Then Me.lstHidden.AddItem Me.lstVisible.List(Index, 0)
            
        lVisibleCount = lVisibleCount - 1
        Me.lstVisible.Selected(Index) = False
        Me.lstVisible.RemoveItem Index
        
        If Not lVisibleCount = 1 Then
          bolProcessing = True
          Exit For
        Else
          Exit Sub
        End If
        
      End If
    Next
  Loop While bolProcessing
  
End Sub
  
Private Sub ShowSheet()
Dim Index             As Long
Dim Index2            As Long
Dim bolProcessing     As Boolean
Dim bolLooking2Insert As Boolean
  
  Do
    bolProcessing = False
    For Index = 0 To (Me.lstHidden.ListCount - 1)
      If Me.lstHidden.Selected(Index) Then
        ThisWorkbook.Worksheets(Me.lstHidden.List(Index, 0)).Visible = xlSheetVisible
        
        bolLooking2Insert = True
        For Index2 = 0 To (Me.lstVisible.ListCount - 1)
          If ThisWorkbook.Worksheets(Me.lstVisible.List(Index2, 0)).Index > ThisWorkbook.Worksheets(Me.lstHidden.List(Index, 0)).Index Then
            Me.lstVisible.AddItem Me.lstHidden.List(Index, 0), Index2
            bolLooking2Insert = False
            Exit For
          End If
        Next
        If bolLooking2Insert Then Me.lstVisible.AddItem Me.lstHidden.List(Index, 0)
            
        lVisibleCount = lVisibleCount + 1
        Me.lstHidden.Selected(Index) = False
        Me.lstHidden.RemoveItem Index
        bolProcessing = True
        Exit For
      End If
    Next
  Loop While bolProcessing
  
End Sub
  
Private Sub cmdHide_Click()
  HideSheet
End Sub
  
Private Sub cmdShow_Click()
  ShowSheet
End Sub
  
Private Sub lstHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  ShowSheet
End Sub
  
Private Sub lstVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  HideSheet
End Sub
  
Private Sub UserForm_Initialize()
Dim WS As Worksheet


  With Me
    .BackColor = &HC8D0D4
    .BorderColor = &H0&
    .Caption = "Hide or Show Sheets"
    .ForeColor = &H0&
    .Height = 200.25
    .Width = 193.5
    With .cmdHide
      .BackColor = &HC8D0D4
      .Caption = "Hide Sheets"
      .ForeColor = &H0&
      .Height = 21.75
      .Left = 96.75
      .Top = 147.75
      .Width = 84.75
    End With
    With cmdShow
      .BackColor = &HC8D0D4
      .Caption = "Display Sheets"
      .ForeColor = &H0&
      .Height = 21.75
      .Left = 6
      .Top = 147.75
      .Width = 84.75
    End With
    With .lblHidden
      .BackColor = &HC8D0D4
      .BorderColor = &H0&
      .Caption = "Currently Hidden"
      .ForeColor = &H0&
      .Height = 9.75
      .Left = 6
      .TextAlign = fmTextAlignCenter
      .Top = 6
      .Width = 84.75
    End With
    With .lblVisible
      .BackColor = &HC8D0D4
      .BorderColor = &H0&
      .Caption = "Currently Visible"
      .ForeColor = &H0&
      .Height = 9.75
      .Left = 96.75
      .TextAlign = fmTextAlignCenter
      .Top = 6
      .Width = 84.75
    End With
    With .lstHidden
      .BackColor = &HFFFFFF
      .BorderColor = &H0&
      .ColumnCount = 1
      .ForeColor = &H0&
      .Height = 120
      .Left = 6
      .MultiSelect = fmMultiSelectExtended
      .TextAlign = fmTextAlignLeft
      .Top = 15.75
      .Width = 84.75
    End With
    With .lstVisible
      .BackColor = &HFFFFFF
      .BorderColor = &H0&
      .ColumnCount = 1
      .ForeColor = &H0&
      .Height = 120
      .Left = 96.75
      .MultiSelect = fmMultiSelectExtended
      .TextAlign = fmTextAlignLeft
      .Top = 15.75
      .Width = 84.75
    End With
  End With
  
  For Each WS In ThisWorkbook.Worksheets
    Select Case WS.Visible
    Case xlSheetVisible
      Me.lstVisible.AddItem WS.Name
      lVisibleCount = lVisibleCount + 1
    Case xlSheetVeryHidden, xlSheetHidden
      Me.lstHidden.AddItem WS.Name
    End Select
  Next
  
End Sub

I tested lightly and don't believe there's any goofs.

Hope that helps,

Mark