Hide all unused worksheet

[Emily]

Is it possible to hide all unused worksheet and recall sheet by a special key?
For example I have 5 sheet, I want the frequently used worksheet, say sheet 1 as default, the other four are normally invisible. A special key, ALT2 or others, is used to invoke sheet 2 and hide sheet 1. The same for the other sheet.

Sorry, One more requirement
The Cancel Hidden command should be inhibitted.

Thanks for your help.
This message was edited by on 2002-09-04 02:20
This message was edited by on 2002-09-04 02:59
This message was edited by Emily on 2002-09-04 08:39

 

[P. Tom]

Check whether this does what you need :-

Private Sub Workbook_Open()
Worksheets("Sheet1").Visible = True
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then ws.Visible = False
Next
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = False
Next
End Sub

 

[Emily]

No. But anyway thanks alot.

The code you wrote is just only to hide the inactive worksheet and cannot make it visible by special key stoke. Also I need the "Cancel Hidden Worksheet" command inhibitted.

Regards.

 

[P. Tom]

A macro could be written so that hidden sheets could be made visible by a special keystroke(one for each sheet), but why not just use Format>Sheet>Unhide.
The code supplied, only allows one sheet to be visible.
Is this not what you want?

"Cancel Hidden Worksheet"?
I don't know what this command is.
However, the code keeps all sheets hidden except the active one - isn't that what you wanted?

 

[stevebausch]

You could hide the sheet tabs.

Start the MacroRecorder, go to Tools/Options/View; uncheck Sheet Tabs.

Stop the macro, look at the code, incorporate the code into the Workbook_Open event , and reset it in the Workbook_BeforeClose event.

Or, don't reset it.....

 

[Richie(UK)]

Hi Emily,

How about something like this:<pre>
Option Explicit
Public iSheet As Integer

'sheet hiding
Sub HideSheets()
Dim ws As Worksheet

Application.ScreenUpdating = False

iSheet = iSheet + 1
If iSheet > Sheets.Count Then iSheet = 1
Worksheets(iSheet).Visible = True

On Error Resume Next
For Each ws In Worksheets
If ws.Name<> Worksheets(iSheet).Name Then
ws.Visible = xlVeryHidden
End If
Next
On Error GoTo 0

Application.ScreenUpdating = True

End Sub</pre>

Place a call to the routine in your Workbook_Open event (it will show sheet1 initially). Assign a key-combination
to the macro - each time it is activated it will make the next sheet visible and all the rest xlVeryHidden.

HTH

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-09-05 05:43

 

[Emily]

Great, Thanks Richie

It works what I think when the following code is added. And most important is the unhide function is disabled.

Application.MacroOptions Macro:="HideSheets", Description:="", ShortcutKey :="s"

Since I am using Chinese Ofiice XP, there is mistake to express "format - worksheet - unhide". Sorry