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.

 

[bjwillingham]

The only way I know to do this is with a macro. Hide the sheet as xlVeryHidden. That way the user can't see it in the sheets collection to unhide. To view the sheet they would have to execute a macro that would ask for a password (hardcoded) or even perhaps held on another 'very hidden' sheet. If they enter the correct password, have the macro unhide the sheet.


Of course you could protect the Workbook with a password, but then that will likely prevent them from doing a lot of other things you want them to do. Then again, maybe not, but it won't re-hide and re-protect the sheet when they are done.

 

[Smitty]

Welcome to the Board!

This is one way:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    <SPAN style="color:#00007F">If</SPAN> pword <> "pword" <SPAN style="color:#00007F">Then</SPAN> ActiveSheet.Visible = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

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

 

[Benoit_G]

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

 

[rzeroth]

Hi,

I found this code on a previous post:

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

but when I paste it into the sheet in VBA I get the following error:

Compile Error
Syntax Error

And it highlights this portion of the code:
("Please Enter a Password", "Unhide Sheets")

Sorry for my ignorance, I am not too familiar with VBA, but where does the actual password go in the code? Right now, I do not have a password in the code...is that why I am getting this error?

Any help is much appreciated!!

 

[Atroxell]

Delete the asterisks if you copied those into the code and replace them with a tab or space... I think they were meant as visual placeholders...

 

[mikerickson]

Since all these approaches require VB, using VeryHidden would probably be easiest.
Also, I don't think the concern about the user seeing the password (if its hard coded into the VB routine) is not really valid. If the user can get into the VB Editor and knows VBA well enough to interpret the password correctly, any security is already blown.

The situation where the user is
1) able to get a password from a VBA module and
2) unable to break the security without that password
is a very rare.

 

[gstullo]

I have workbook that un-hides sheets when the correct password is entered... This is great but is there anyway to hide the password when it is entered?

Like instead of having "password" display when entered, have "********" display?

Thanks in advance.

GT

 

[lenze]

I have workbook that un-hides sheets when the correct password is entered... This is great but is there anyway to hide the password when it is entered?

Like instead of having "password" display when entered, have "********" display?

Thanks in advance.

GT

See here

lenze

 

[GTO]

There are examples about of using API to coerce an InputBox into displaying asterisks. IMHO, it is easier to make a simple userform with one textbox and two command buttons (OK and Cancel).

Create a new userform and plop a textbox onto it. In the properties window, check out the PasswordChar property.

Hope that helps,

Mark