Protect or Hide all worksheets in a workbook except 1

  • Thread starter Thread starter Gator Girl
  • Start date Start date
G

Gator Girl

I have a workbook with 61 worksheets, 60 of which I do not want to be seen or
used without use of a password. I want a single password to open up all 60
hidden worksheets to view and to edit.

The worksheets each have a different employee’s name. So I need to know if
I have to write the code to the specific sheet names, or if it will work with
“Sheet1†etc no matter what the sheet’s name is.

I ask this because employee’s come and go and once I have the successful
code I need to know if I have to edit it each time we gain or lose an
employee.

I have dutifully read all possible applicable info I could find, and tried a
couple of solutions but no luck.
 
Hi,

First the ususal cautionary note, this isn't secure, Excel protection is
designed to protect against accidental deletions etc and not to provide high
levels of security.

One way.

Alt+F11 to open VB editor and double click 'ThisWorkbook' and paste this in
on the right

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For x = 2 To Worksheets.Count
Sheets(x).Visible = xlVeryHidden
Next
End Sub

Then right click 'Thisworkbook' and insert module and paste this in. Put a
button on the one remaining visible sheet that calls this code. The visible
sheet will be the leftmost sheet irrespective of name. Change Mypass to your
password

Sub View_Sheets()
response = InputBox("Enter password", "Password")
If response <> "Mypass" Then Exit Sub
For x = 2 To Worksheets.Count
Sheets(x).Visible = True
Next
End Sub

Mike
 
Sub Hide_Sheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
If Sheets(N).Name <> "Sheet1" Then 'substitute the sheet name
Sheets(N).Visible = xlVeryHidden
End If
Next N
Application.ScreenUpdating = True
End Sub

Sub Unhide_Sheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Visible = True
Next N
Application.ScreenUpdating = True
End Sub

Once you have pasted these to a module in your workbook, select the project
and right-click>VBAProject Properties.

Lock the project from view with a good password.............keep it safe.

Save the workbook.

To run the macros type the name into the Macros dialog box or unlock the
project and run.

Don't forget to re-lock.


Gord Dibben MS Excel MVP
 
Back
Top