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.
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.
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)
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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!!
put this in the ThisWorkbook code module
[vba]
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
[/vba]
Put this in a standard module (call it from the Macro dialog box)
[vba]
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
[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I like it John...!
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Yeh, looks pretty neat huh?Originally Posted by lucas
Probably better to use something like a floating toolbar or a right-click on the ply toolbar to activate (hide/unhide) sheets tho...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
How about a table of contents? See KB Item "Create Table of Contents"
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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...
[VBA]
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
[/VBA]
This code goes in a standard code module...
[VBA]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[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi. Thanks for all your suggestions. It works very well.
Thanks so much!!