Password Protect Worksheets/Tabs

mdberg

New Member
Joined
Dec 18, 2009
Messages
2
I have an Excel 2007 document with various worksheets/tabs, several of which I would like hidden and password protected. I do want to present 2 tabs to the user, but hide the rest. Is there a way to hide tabs and create a password to view (or un-hide) the tabs?

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I want the user to be able to:
- Open the document and view only the tabs which are not hidden
- Not be able to un-hide tabs without a password

<o:p></o:p>
Any advice would be helpful. Thanks!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

To protect a sheet, right click its tab and select Protect Sheet. Then right click again and select Hide.

Note that Excel passwords are very easy to crack.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016
Hi Michael.

Probably the best thing for you would be to make the sheets 'very hidden' this does require the use of VBA and the syntax is

Code:
Sheets("MySheet").Visible = xlSheetVeryHidden

This will stop it from being viewed, you would then apply a password to your VBA Project to ensure the users were not aware that there were hidden sheets. It's not 100% secure (like most things in Excel) but so long as your users aren't determined to screw your sheets you should be fine.
 

mdberg

New Member
Joined
Dec 18, 2009
Messages
2
Thanks- I think this should work, it sounds like what I'm looking to do.

I know that the passwords in Excel aren't the most secure, but we are presenting some electronic documents to a third party in our office (on our laptops with internet/CD/USB drives/select programs disabled) and need the password just incase. It's mostly to show them that "your not supposed to see this part just yet!"
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016
Well a code to unhide the sheets would look something like

Code:
Sub UnhideSheets()
Dim pWord As String
Dim ws As Worksheet
pWord = InputBox("Please enter password to unhide sheets")
If pWord = "MyPassword" Then
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End If
End Sub

And could be applied to either a forms or activeX control

The code to hiden them would go something along the lines of

Code:
Sub HideSheets()
Dim ws As Worksheet
Dim pWord As String
pWord = InputBox("Enter Password")
If pWord <> "Password" Then Exit Sub
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then GoTo 0
            ws.Visible = xlSheetVeryHidden
0
    Next ws
End Sub
 

Forum statistics

Threads
1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

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