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.

 

[erinwashere]

It is possible to wrestle this VBA code into allowing the user to view hidden columns?

Thanks!

 

[wilcowoods]

To hide the password why not just make it a variable that corresponds to a cell on the hidden sheet? That way the user can regularly change the password from the hidden sheet without knowing any VBA.

You could surely even use an event procedure to force a password change every week or month! Just a thought for the super paranoid!

 

[Excel777]

can anyone explain to me
this line "C:\UPC\excel_password.txt" For Input As #iFileNo

thaks in advance

 

[mikerickson]

The Sub password_request is opening the text file excel_password.txt to compare whether the user entered password matches the one stored in the file.

 

[Excel777]

ok dear
if my understanding true to this line
text file named "excel_password" in folder "UPC" in Partition "C" and the password in the text file "#iFileNo"

is this true?

coz i did that and give me error when i try to unhide sheet

 

[Taul]

generally, I would put the password in a cell on the hidden sheet and refer to it as:-

If strPassword <> Sheet1.Range("az1") Then MsgBox "Wrong Password": Exit Sub

that way, it can't easilly be seen via another program. But as Mike says, the likelyhood of something happening needs to be considered.

 

[Excel777]

plz can i have the complete vba code if i use this

"If strPassword <> Sheet1.Range("az1") Then MsgBox "Wrong Password": Exit Sub"

thanks in advance.

 

[Taul]

Ok, this is straight out of an existing sheet, so you will have to adjust the references to suit your layout. You will also need to make a simple userform to request the password is entered.

Private Sub CommandButton1_Click()
'This asks for a password before show / hide sheets will work
Dim Ws As Worksheet
Dim strPassword As String
strPassword = TextBox1.Value
If strPassword <> Sheet5.Range("az1") Then MsgBox "Wrong Password": Exit Sub

'This will toggle show / hide the data sheets

Application.ScreenUpdating = False

Sheet5.Visible = xlSheetVisible 'this is the hidden sheet that commandbutton1 will unhide
Sheet2.Unprotect Password:=Sheet5.Range("az1") ' removes protection from the sheet
Sheet1.Unprotect Password:=Sheet5.Range("az1")
'Application.CommandBars("Tools").Controls(18).Enabled = True 'enables the Options command that was turned off previously - only good for V2003 drop down menu - don't use for 2007 ribbon

Unload UserForm1 ' removeds the text from the userform
UserForm1.Hide ' get rid of it

Application.ScreenUpdating = True

End Sub

 

[Excel777]

thanks dear PAUL-H For Answering me .
Godl Bless You.
Best Regards

 

[z1ruam]

I have the same question but I'm using Excel 2007 and I send my file to someone who knows a bit of vb programming and might be able to see the password.

I am a noob in vb and from Paul-H's code, is az1 the password?

To hide the password why not just make it a variable that corresponds to a cell on the hidden sheet? That way the user can regularly change the password from the hidden sheet without knowing any VBA.

You could surely even use an event procedure to force a password change every week or month! Just a thought for the super paranoid!

The above quote is also a great idea but how would I do that?