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" wrote in message
...
Checkboxes to show/hide tabs...
|