Hide a sheet and Password protect it

[adityad]

I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.

 

[wilcowoods]

I agree that it would be bullet proof however it relies on the user to hide the sheets before saving. There is no way to guarantee that they do this! Too much risk!

Not at all - you use a "before save" event handler that hides the sheet! The user has no control over this!

 

[thosecars82]

One may not want the password to be found directly in the sheet code. Here comes an improved version of Smitty:

Private Sub Worksheet_Activate()
Dim pswrd As String
Dim pword As String
Call password_request(pswrd)
pword = InputBox("Please Enter a Password", "Unhide Sheets")
If pword <> pswrd Then ActiveSheet.Visible = False
End Sub

Sub password_request(pswrd As String)
Dim iFileNo As Integer
iFileNo = FreeFile
'open the file for reading
Open "C:\UPC\excel_password.txt" For Input As #iFileNo

' Read password
Input #iFileNo, pswrd

'close the file
Close #iFileNo
End Sub

I tried the code and it works great. Is there any way to avoid having to input the password several times when we switch from one sheet to another and then we come back to the protected sheet?

Thanks

 

[anum]

Welcome to the Board!

This is one way:

Private Sub Worksheet_Activate()
****pword = InputBox("Please Enter a Password", "Unhide Sheets")
****If pword <> "pword" Then ActiveSheet.Visible = False
End Sub

The code goes in the module specific to the sheet that is hidden. When the user selects Format-->Sheet-->Unhide, the sheet will be unhidden, but they will be asxked for a pasxsword. If they get it wrong, the sheet will be rehidden.

Hope that helps,

Smitty

Hi there, I tried this today when its set under hidden and when i right clicked and selected unhide, for some reason, it didnt ask for the password! my excel file is saved down as an xlsm...

 

[Patalanoyo]

The Code works very good, but it requests the passcode when the file is being opened.

could you please advice

I don't want it to ask for a password each time the file is being opened.

Thank you

 

[AtlantaKid]

I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.

Here is an easy way which will password protect it without VB etc.. I am using Excel 2013

1 - go the worksheet you want to hide, select all the columns that you want to hide
2 - on the ribbon click on DATA and then click on GROUP, let excel group your selected columns
3 - click on the "-" sign to fold up all your columns, so they are not visible now
4 - right click on TAB of exiting worksheet (at the bottom) choose "Protect Sheet..." , use the defaults that are presented to you and give it a password (make sure you remember what you used as password)
5 - Save the excel document.

Now if you go back to the document you wont see the data in the protect sheet since it is all folded up, and if you click on the "+" to unfold it, it will ask you to unprotect using the password.