Password protect 1 worksheet from viewing

kelly1

Well-known Member
Joined
May 11, 2003
Messages
525
Is it possible to password protect just one worksheet in a workbook from viewing?

The worksheet is called USERS


Thanks for any help

Kelly
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
You can hide the sheet

Format>Sheets>Hide

and the Tools>Protection>Protect Workbook>Structure.

YOu can enter a password there.
 
L

Legacy 51064

Guest
You can protect the sheet by:


Format
Sheets
Hide

Then:

Tools
Protection
Protect sheet.

Excel is not a very secure program.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can use VBA to set the sheet as VeryHidden, which can only be unhidden through VBA.

The following code will test for your user name and if it's not you, hide the sheet when the wb is opened:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> AdminUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        AdminUser = Environ("username")
        
        <SPAN style="color:#00007F">If</SPAN> AdminUser <> "yourusername" <SPAN style="color:#00007F">Then</SPAN>
            Sheets("Users").Visible = xlVeryHidden
        Else: Sheets("Users").Visible = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Kelly,

so you mean the worksheet would be hidden ?
you could put a button on a sheet to show it or invent some secret trick to show it (like putting a certain value in a cell to activate the code)
anyway you will have to lock your project from viewing

Code:
Sub test()
If InputBox("code please", "TITLE", "") <> "yourcode" Then Exit Sub
ThisWorkbook.Unprotect "password"
    With Sheets("USERS")
    .Visible = True
    .Activate
    End With
ThisWorkbook.Protect "password"
End Sub
to hide the sheet when leaving
Code:
Private Sub Worksheet_Deactivate()
    ThisWorkbook.Unprotect "password"
    Me.Visible = xlSheetVeryHidden
    ThisWorkbook.Protect "password"
End Sub
you could make this conditional (checking who is using the workbook with Environ("username") ... or checking the "secret-trick-cell")

would this get you further ?

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,299
Latest member
agentless
Top