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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,778
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top