|
|
|
|
|
|
Excel
|
A Better Sheet Management Function
|
|
Ease of Use
|
Intermediate
|
Version tested with
|
XL97, 2000, XP, 2003
|
Submitted by:
|
Bob Phillips
|
Description:
|
Improved facilities to hide and unhide sheets in Excel
|
Discussion:
|
In Excel, the built-in functions to hide and unhide worksheets is via the menu Format>Sheet>Hide (or Unhide...). In addition, although more than one worksheet can be hidden (by selecting multiple sheets), only one sheet can be unhidden at a time. This utility provides the ability to unhide multiple sheets. It adds two menu items to the sheet tab right-click menu, demonstrating how to add items to that menu as well.
NB Although it would have been easy to handle very hidden worksheets, it has not been done in this code as it would be providing access to those sheets that cannot be gained in Excel, so I thought best to maintain that status quo. I leave as an exercise for the reader if so desired.
|
Code:
|
instructions for use
|
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 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
Option Explicit
Option Private Module
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
Private Sub UnhideSheet()
frmUnhideSheets.Show
End Sub
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
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
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call MenuRemovePly
End Sub
Private Sub Workbook_Open()
Call MenuAddPly
End Sub
|
How to use:
|
- This code consists of 3 components
- - a userform for displaying the hidden sheets and provide options
- - workbook event code to initiate the menu build
- - code to create the menus
-
- Open a Excel workbook, or use an existing workbook (such as Personal.xls)
-
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Tools/Project Explorer
-
- Select the ThisWorkbook module
- Copy and paste the 'ThisWorkbook' code section above into this module
-
- Insert a new code module
- Name the new module mPly,
- Copy and paste the and add the 'mPly' code section above to that module
-
- Insert a new Userform module into your workbook
- Start by adding the following controls
- - a listbox
- - 4 commandbuttons
- Do not worry about the placement of the controls, the code resets it all correctly.
- Now add the code the 'frmUnhideSheets' code section into this module
-
- Now select File/Save for your file
|
Test the code:
|
- Close Excel down
- Start Excel again (to force a menu build)
- If you didn't use Personal.xls, open the workbook with code
- Open a new workbook, or any existing workbook
- Right-click on a sheet tab, you will see the options
- If you choose 'Hide Sheet(s)', it will hide the selected sheet(s)
- If you choose 'Unhide Sheet(s)...', it will present a dialog box showing all hidden sheets.
|
Sample File:
|
xld.ManageSheets.zip 19.11KB
|
Approved by mdmackillop
|
This entry has been viewed 715 times.
|
|