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”
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Hiding Sheet Tabs
- Thread starter Daved
- Start date
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:.
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:.
Upvote
0
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.
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.
Upvote
0
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.
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.
Upvote
0
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.
Mine is far from perfect but at least requires the user to enter a password before sheets can be unhidden.
Upvote
0
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.
Upvote
0
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
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
Upvote
0
