Hiding Sheet Tabs

Daved

New Member
Joined
Sep 12, 2002
Messages
15
We have cost sheets that have 6 tabs for inputting data and one summary sheet. I'd like only the summary sheet to be visible to our creative people. Checking a box on the summary sheet hides the others. Entering a code on the summary sheet brings them back. If that is possible you are a “GOD”
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
There's a few ways, all with drawbacks.
First way is automated through VBA: -

1. Insert a new worksheet and in cell A1 type in the password you want to use
2. In your 'Summary' sheet add a CheckBox through the Controls toolbox and, in design mode, double-click on the checkbox and copy and paste in the following code: -

<pre>
Private Sub CheckBox1_Click()

Dim strPassWord As String
Dim i As Integer
Dim ws As Worksheet

If CheckBox1.Value = True Then
Do Until i = 3
strPassWord = InputBox("Enter Password")
If strPassWord = Sheet4.Range("A1") Then
ThisWorkbook.Unprotect strPassWord
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Exit Sub
Else:
i = i + 1
MsgBox "Invalid password"
End If
Loop
Else:
Exit Sub
End If

MsgBox "You do not have authorisation to view other worksheets", vbExclamation, _
"Please contact your manager"

End Sub
</pre>

3. While you're in the VBA editor, double-click on the entry for ThisWorkbook and copy and paste in the follwoing: -

<pre>
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet

ThisWorkbook.Worksheets("Summary").CheckBox1.Value = False
ThisWorkbook.Unprotect Sheet4.Range("A1")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then ws.Visible = xlSheetHidden
Next ws
ThisWorkbook.Protect Sheet4.Range("A1").Text

ThisWorkbook.Save

End Sub
</pre>

4. In both the previous procedures, replace each instance of 'Sheet4' with the object name of the worksheet you've just inserted in step 1. The object name will be in the explorer pane to the left.

5. Not really necessary, but probably good practice for this kind of thing- Hide all the worksheets you don't people to see then protect the workbook with the exact text (case-sensitive) you've put into cell A1 in step 1.

6. Protect the VBA project, from within the VBA editor, click on Tools-VBAProject Properties, click on the Protection tab, the Lock Project for Viewing box and enter a password.

7. Close and save a copy of your workbook and see if it works out how you wanted.

8. Repost if and when the above does not work out how you wanted, and I or another person'll put up a more suitable method :wink:.
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
Hi,

Although Mudface already came up with a solution, i'll post mine anyway.

1. Open the VB editor (Alt F11)
2. Hit Ctrl+R to ensure that the Project Explorer is visible
3. In there you should be able to see your workbook saying something like VBAProject (Workbookname.xls)
4. Double click on the Thisworkbook icon
5. The code module opens up
6. Paste this code into it:

<pre>
Sub hide()
For Each sh In Sheets
If sh.Name <> "Summary" Then sh.Visible = xlSheetHidden
Next
End Sub
Sub unhide()
For Each sh In Sheets
sh.Visible = xlSheetVisible
Next
End Sub
</pre>
7. Double click on the Sheet summary icon
8. Paste this code into it:

<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
' you can change the secret word "Dave" to anything you want
If Target.Value = "Dave" Then ThisWorkbook.unhide
End Sub
</pre>
9. Create a button (from the Forms toolbar) on the summary sheet and assign macro "Thiswookbook.hide"


When the button is clicked the sheets will hide.
When the text "Dave" will be entered in a cell on the summary sheet all sheets will be visible.
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423

ADVERTISEMENT

Right first things first this requires VBA. open editor by pressing Alt+F11. In the thisworkbook code type

Private Sub Workbook_Open()
ActiveWindow.DisplayWorkbookTabs = False
Sheets("Summary Sheet").Select
End Sub

This turns off the tabs and ensures that when the workbook is opened the summary sheet is displayed.

Now in the summary sheet write the following code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells(1, 1), Sheets("Summary Sheet").Range("A1")) Is Nothing Then
If Target.Cells(1, 1).Value = "bob" Then
ActiveWindow.DisplayWorkbookTabs = True
Else
ActiveWindow.DisplayWorkbookTabs = False
End If
End If
End Sub

This code means that if the code word bob is placed in A1 the sheet tabs are displayed.

Hope this helps. Let us know if this is what you want.
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Rikrak and Bolo- both your solutions will work but can be easily subverted as the workbook has not been password protected. In Bolo's case selecting Options- View- Sheet Tabs will allow the user access to all sheets. In Rikrak's case, selecting Format- Sheet Unhide will allow the user to view the restricted sheets.

Mine is far from perfect but at least requires the user to enter a password before sheets can be unhidden.
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423

ADVERTISEMENT

fair enough. it was only a quick solution. but point taken.

Its fairly easy to get past all the solutions just by turning off macros.
This message was edited by bolo on 2002-10-05 18:37
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
Mudface,

My solution does exactly what the OP requested: "Entering a code on the summary sheet brings them back"

Yes, i replaced the checkbox with a commandbutton. :)
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
On 2002-10-05 18:31, rikrak wrote:
Mudface,

My solution does exactly what the OP requested: "Entering a code on the summary sheet brings them back"

Yes, i replaced the checkbox with a commandbutton. :)

Yes, but it allows any user to unhide any sheet they want through Format-Sheet-Unhide without a password. In addition it could open the workbook with the 'restricted' sheets visible and available for editing.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
These macros are as simple as they get. In this example, I have renamed Sheet3 to "Summary".

Put both macros in a standard module:

1. When in Excel: Alt-F11 to take you to the VBA Editor.
2. While in the VBA Editor, go to the Insert menu and select Module. Module1 will be inserted into your project.
3. Copy and paste the macros into Module 1.
4. Return to Excel.<pre>
Sub HideSh()
Application.ScreenUpdating = False

Worksheets("Summary").Visible = True
Worksheets("Sheet1").Visible = xlHidden
Worksheets("Sheet2").Visible = xlHidden
Worksheets("Sheet4").Visible = xlHidden
Worksheets("Sheet5").Visible = xlHidden
Worksheets("Sheet6").Visible = xlHidden

Application.ScreenUpdating = True

End Sub

Sub UnHideSh()
Application.ScreenUpdating = False

Worksheets("Summary").Visible = True
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
Worksheets("Sheet4").Visible = True
Worksheets("Sheet5").Visible = True
Worksheets("Sheet6").Visible = True

Application.ScreenUpdating = True

End Sub

The UnHide macro can be shorten to:

Sub GlobalUnhide()

For Each sh In Sheets
sh.Visible = True
Next sh

End Sub</pre>


In Excel:

a) Select your "Summary" sheet
b) Go to View menu/Toolbars/Forms
c) Select a button, right click it and assign button 1 to the Hide macro.
d) Right click the button again, select "Edit Text" and change the name of the button to "Hide" (without the apostrophes).

Do the above steps a) to c) for the Unhide macro.

If you really want only one Hide/Unhide switch on your "Summary" sheet, I can walk you through that using a check box. A check box can be either "True" (i.e. activate the Hide macro) or "False" (activate the UnHide macro). Meanwhile, see how you go with the two-button routine.

Regards,

Mike
This message was edited by Ekim on 2002-10-05 19:19
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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