Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect or Hide all worksheets in a workbook except 1
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 employees 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 sheets name is. I ask this because employees 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect or Hide all worksheets in a workbook except 1
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 "Gator Girl" wrote: 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 employees 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 sheets name is. I ask this because employees 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect or Hide all worksheets in a workbook except 1
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-clickVBAProject 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 On Tue, 3 Feb 2009 11:02:14 -0800, Gator Girl wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect/unprotect ALL worksheets in workbook ? | Excel Discussion (Misc queries) | |||
Hide and protect worksheets depending upon who is looking? | Excel Discussion (Misc queries) | |||
protect worksheets in workbook | Excel Worksheet Functions | |||
Hide and PW protect multiple worksheets | Excel Worksheet Functions | |||
how do i protect all worksheets in a workbook at one time | Excel Discussion (Misc queries) |