Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Check boxes to show/hide Tabs
Checkboxes to show/hide tabs...
|
#2
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
Creating Formula using check boxes | Excel Discussion (Misc queries) | |||
Creating a check box that does not require security clearance. | Excel Worksheet Functions | |||
Sheet tabs disappear sometimes in Internet Explorer | Excel Discussion (Misc queries) | |||
how to incert multiple check boxes in excel | Excel Worksheet Functions |