Solved: Hide sheets and show only if i call them

[noobie]

Dear all,
I have lots of sheet tabs. And I really find the tabs very cluttered and the scroll button very annoying. I really hope I can hide the sheets and show only if i call them.

Thanks in advance for your kind help,

Noobie.

[johnske]

That can be done, but before you go down that route - have you tried selecting sheets by right-clicking the bottom-left corner of the worksheet? (i.e. right-click on the scroll buttons)

[noobie]

hi. Thanks for your reply.

I did. But in consideration to the many users coming across my program, I would want to show a neat display. And I wonder if there's a way to make everything look sweet.

Thanks!!

[johnske]

put this in the ThisWorkbook code module

Option Explicit
'
Private Sub Workbook_Open()
'
Call HideSheets
'
End Sub
'
Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
Sheets("Sheet1").Visible = xlSheetVisible
'
For Each Sheet In Sheets
'change name to suit
If Not Sheet.Name = "Sheet1" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
End Sub


Put this in a standard module (call it from the Macro dialog box)

Option Explicit

Sub UnhideSheet()
'
Dim ThisSheet As String
'
ThisSheet = ActiveSheet.Name
If Application.Dialogs(xlDialogWorkbookUnhide).Show = True Then
Sheets(ThisSheet).Visible = xlVeryHidden
End If
'
End Sub

[lucas]

I like it John...!

[johnske]

I like it John...!Yeh, looks pretty neat huh?

Probably better to use something like a floating toolbar or a right-click on the ply toolbar to activate (hide/unhide) sheets tho... :)

[mdmackillop]

How about a table of contents? See KB Item "Create Table of Contents"

[johnske]

Thanx Malcolm, that's another option.

Noobie, try this variation. This adds a "Show Sheet" control at the top of the "Ply" toolbar (you get to this control by right-clicking the worksheet tab), there's also provision for adding and deleting a sheet in this version.

Code for the ThisWorkbook code module...

Option Explicit
'
Private Sub Workbook_Open()
Call HideSheets
Call AddToolbar
End Sub
'
Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'//change name to suit\\
Const IntroSheet As String = "Sheet1"
'
Application.ScreenUpdating = False
Sheets(IntroSheet).Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = IntroSheet Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
Application.ScreenUpdating = False
End Sub
'
Private Sub AddToolbar()
Call RemoveToolbar
With Application.CommandBars("Ply")
With .Controls.Add(before:=1)
.Caption = "Show Sheet"
.OnAction = "ShowSheet"
End With
.Controls.Item(2).BeginGroup = True
'add a custom delete to intercept any ''delete'' action
.Controls("Delete").Delete
With .Controls.Add(before:=4)
.Caption = "&Delete"
.OnAction = "DeleteSheet"
End With
End With
End Sub
'
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.ScreenUpdating = False
For Each Sh In Sheets
If Not Sh.Name = ActiveSheet.Name Then
Sh.Visible = xlSheetVeryHidden
End If
Next
Application.ScreenUpdating = True
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveToolbar
End Sub
'
Sub RemoveToolbar()
Application.CommandBars("Ply").Reset
End Sub



This code goes in a standard code module...
Option Explicit
'
Private Sub ShowSheet()
'
Dim ThisSheet As String
'
Application.ScreenUpdating = False
ThisSheet = ActiveSheet.Name
'
If Application.Dialogs(xlDialogWorkbookUnhide).Show = True Then
Sheets(ThisSheet).Visible = xlVeryHidden
End If
Application.ScreenUpdating = True
End Sub
'
Private Sub DeleteSheet()
'
Dim ThisSheet As String
'
Application.ScreenUpdating = False
ThisSheet = ActiveSheet.Name
'
If Not ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet1").Visible = True
Sheets(ThisSheet).Delete
End If
Application.ScreenUpdating = True
End Sub

[noobie]

Hi. Thanks for all your suggestions. It works very well.

Thanks so much!!