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:.