Hide all unused worksheet

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

P. Tom

New Member
Joined
Jun 30, 2002
Messages
14
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

Active Member
Joined
Aug 28, 2002
Messages
304
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

New Member
Joined
Jun 30, 2002
Messages
14
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

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

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)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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>

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

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
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
 

Forum statistics

Threads
1,144,148
Messages
5,722,792
Members
422,458
Latest member
Muirzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top