'-------------------------------------------------
 ' frmUnhideSheets code module
 '-------------------------------------------------
 ' Function: To provide a userform showing
 '           hidden worksheets and giving
 '           a user interface to manage same
 '-------------------------------------------------
 
Private Sub UserForm_Initialize() 
    Dim i As Long 
    With Me 
        .Top = 0:  .Left = 0:  .Height = 145: .Width = 240 
        With .ListBox1 
            .Top = 12:  .Left = 8:  .Height = 100: .Width = 160 
        End With 
        With .CommandButton1 
            .Top = 12: .Left = 174: .Height = 20: .Width = 54 
            .Default = True 
            .Caption = "OK" 
        End With 
        With .CommandButton2 
            .Top = 36: .Left = 174: .Height = 20: .Width = 54 
            .Cancel = True 
            .Caption = "Cancel" 
        End With 
        With .CommandButton3 
            .Top = 60: .Left = 174: .Height = 20: .Width = 54 
            .Caption = "Select All" 
        End With 
        With .CommandButton4 
            .Top = 84: .Left = 174: .Height = 20: .Width = 54 
            .Caption = "Deselect All" 
        End With 
    End With 
     
    ListBox1.Clear 
    With ActiveWorkbook 
        For i = 1 To .Sheets.Count 
            If .Sheets(i).Visible = False Then 
                ListBox1.AddItem (.Sheets(i).Name) 
            End If 
        Next 
    End With 
End Sub 
 
Private Sub CommandButton1_Click() 
    Dim i As Long 
    Unload frmUnhideSheets 
    Application.ScreenUpdating = False 
    For i = 0 To ListBox1.ListCount - 1 
         'If an item is selected, unhide that sheet.
        If ListBox1.Selected(i) = True Then 
            With ActiveWorkbook.Sheets(ListBox1.List(i)) 
                .Visible = True 
                .Activate 
            End With 
        End If 
    Next 
End Sub 
 
Private Sub CommandButton2_Click() 
    Unload frmUnhideSheets 
End Sub 
 
Private Sub CommandButton3_Click() 
    Dim i As Long 
    For i = 0 To ListBox1.ListCount - 1 
        ListBox1.Selected(i) = True 
    Next 
End Sub 
 
Private Sub CommandButton4_Click() 
    Dim i As Long 
    For i = 0 To ListBox1.ListCount - 1 
        ListBox1.Selected(i) = False 
    Next 
End Sub 
 
 '-------------------------------------------------
 ' frmUnhideSheets code module end
 '-------------------------------------------------
 
 
 
 
Option Explicit 
Option Private Module 
 
 '-------------------------------------------------
 ' mPly standard code module
 '-------------------------------------------------
 ' Function:	Hides all selected worksheets
 '		selected tabs that is
 '-------------------------------------------------
Private Sub HideSheet()Dim cVisible As Long 
    Dim i As Long 
    With ActiveWorkbook 
        For i = 1 To Worksheets.Count 
            If .Worksheets(i).Visible = xlSheetVisible Then 
                cVisible = cVisible + 1 
            End If 
        Next i 
         
    End With 
     
    With ActiveWindow 
        If cVisible > .SelectedSheets.Count Then 
            .SelectedSheets.Visible = False 
        Else 
            MsgBox "You cannot hide this sheet, as it" & vbNewLine & _ 
            "will not leave a visible sheet, and" & vbNewLine & _ 
            "that is not permissible with Excel", vbInformation, _ 
            "Sheet Management" 
        End If 
    End With 
End Sub 
 '-------------------------------------------------
 ' Function:	Calls the userform to provide
 '		options on unhiding sheets
 '-------------------------------------------------
Private Sub UnhideSheet() 
    frmUnhideSheets.Show 
End Sub 
 
 '-------------------------------------------------
 ' Function:	Adds two new items to the Ply menu
 '		(the menu that you get when you
 '		right-click the sheet tag), one to
 '		Hide selected sheets, one to give
 '		an Unhide sheets dialog
 ' Calls:	MenuRemovePly
 '		to remove the items if already on
the Ply menu, To avoid dplication 
 '-------------------------------------------------
Public Sub MenuAddPly() 
     
    MenuRemovePly 
     
    With Application.CommandBars("Ply") 
        .Controls.Add(Type:=msoControlButton).Caption = _ 
        "Hide Sheet(s)" 
        .Controls.Add(Type:=msoControlButton).Caption = _ 
        "Unhide Sheet(s)..." 
        .Controls("Hide Sheet(s)").BeginGroup = True 
        .Controls("Hide Sheet(s)").OnAction = "HideSheet" 
        .Controls("Unhide Sheet(s)...").OnAction = "UnhideSheet" 
    End With 
End Sub 
 
 '-------------------------------------------------
 ' Function:	Removes the two items from the Ply
 '		menu
 '-------------------------------------------------
Public Sub MenuRemovePly() 
    On Error Resume Next 
    With Application.CommandBars("Ply") 
        .Controls("Hide Sheet(s)").Delete 
        .Controls("Unhide Sheet(s)...").Delete 
    End With 
    On Error GoTo 0 
End Sub 
 
 '-------------------------------------------------
 ' mPly standard code module end
 '-------------------------------------------------
 
 
 
 
Option Explicit 
 
 '-------------------------------------------------
 ' ThisWorkbook code module
 '-------------------------------------------------
 ' Function:	Calls the Ply menu add routine in
 '		the workbook open event, and the
 '		Ply menu remove routine in the
 '		workbook beforeclose event
 '-------------------------------------------------
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call MenuRemovePly 
End Sub 
 
Private Sub Workbook_Open() 
    Call MenuAddPly 
End Sub 
 
 '-------------------------------------------------
 ' ThisWorkbook code module end
 '-------------------------------------------------