Check boxes to show/hide Tabs

Checkboxes to show/hide tabs...

Sridhar,

Here is a way which adds a couple of options to the sheet tab right-click
(Ply) menu.

First build a form with a listbox and 4 commandbuttons, with name & captions
of

lstSheete & null
cmdOK & OK
cmdCancel & Cancel
cmdSelectAll & Select All
cmdDeselectAll & Deselect All

Add this code to tyhe form

Option Explicit

Private Sub UserForm_Initialize()
Dim i As Long
lstSheets.Clear
With ActiveWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i).Visible = False Then
lstSheets.AddItem (.Sheets(i).Name)
End If
Next
End With
End Sub

Private Sub cmdCancel_Click()
Unload frmUnhideSheets
End Sub

Private Sub cmdOK_Click()
Dim i As Long
Unload frmUnhideSheets
Application.ScreenUpdating = False
For i = 0 To lstSheets.ListCount - 1
'If an item is selected, unhide that sheet.
If lstSheets.Selected(i) = True Then
With ActiveWorkbook.Sheets(lstSheets.List(i))
.Visible = True
.Activate
End With
End If
Next
End Sub

Private Sub cmdSelectAll_Click()
Dim i As Long
For i = 0 To lstSheets.ListCount - 1
lstSheets.Selected(i) = True
Next
End Sub

Private Sub cmdDeselectAll_Click()
Dim i As Long
For i = 0 To lstSheets.ListCount - 1
lstSheets.Selected(i) = False
Next
End Sub


Then put this code in the ThisWorkbbok module to build the menu

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MenuRemovePly

End Sub

Private Sub Workbook_Open()

MenuRemovePly
MenuAddPly

End Sub


And finally, put this code in a standard bas module

Private Sub HideSheet()
ActiveWindow.SelectedSheets.Visible = False
End Sub

Private Sub UnhideSheet()
frmUnhideSheets.Show
End Sub

Public Sub MenuAddPly()
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

Public Sub MenuRemovePly()
With Application.CommandBars("Ply")
.Controls("Hide Sheet(s)").Delete
.Controls("Unhide Sheet(s)...").Delete
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sridhar Rao" <[email protected]> wrote in message
news:[email protected]...
> Checkboxes to show/hide tabs...