Protecting Sheet to Prevent Viewing At All

M

Michael Link

Is it possible to protect a sheet in a workbook so that it isn't even
viewable unless you have the password? One of the sheets in my workbook
contaiins highly sensitive information that I don't want most folks to see,
but I want users to be continue to hide and unhide other elements in the
workbook to fit their needs.

Is there something I can write in VBA that will do the trick? In the perfect
world, what would happen is that, when a user clicked on the tab for the
Sensitive Sheet, a popup box would appear asking for a password. Is this even
possible?

Help! Any answers would be much appreciated.

Cheers!
 
V

VoG

The following code will make the sheet 'invisible' to ordinary users:

Sub hideit()
Sheets("Sheet2").Visible = xlVeryHidden
End Sub

To unhide it use

Sub unhideit()
Sheets("Sheet2").Visible = True
End Sub
 
H

Hayeso

Right Click the Sheet tab and select "View Code" then add
the following code. You can change the password to whatever you like.


Private Const Password As String = "DoubleOhSeven"

Private Sub Worksheet_Activate()
Me.Visible = False
If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
"Enter Password") <> Password Then
Me.Visible = False
Else
Me.Visible = True
End If
End Sub
 
M

Michael Link

As Mr. Burns would say, EXCELLENT! This will be very helpful. Thank you so
much!
 
M

Michael Link

Both this and the other reply, below, will be phenomenally useful. Thank you
both! The Excel rescue team comes through again!
 
M

Michael Link

Hi--

Actually, I just had a few minutes to try to implement this. Unfortunately,
I'm gettiing a "Syntax Error" message, indicating that there's an
end-of-statement issue. Might you have any ideas what the issue is?

Thanks!
 
D

Dave Peterson

Worksheet and workbook protection (via tools|Protection) is very weak and isn't
meant for security.

I wouldn't share any highly sensitive data in excel.

(If the user disables macros/events, then no macro solution will help.)

And if a user can find this newsgroup or google, they can find ways to unprotect
your workbook/worksheet.

Be very careful with what you share in excel.
 
N

Norman Jones

Hi Michael,

one line of Hayeso's code has wrapped making it appear as two lines. To
obviate the problem, replace:
If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
"Enter Password") <> Password Then
Click to expand...

with:

If InputBox("Please Enter the Password for the " _
& Me.Name & " Sheet", "Enter Password") _
<> Password Then
 
C

coddave

Hi Norman,

Thanks for the help! I also required some information to be restricted. Out
of curiosity after unhiding the cell, how do you go in at a later time to
change the password? When I click on the sheet it continues to bring the
password screen up over and over. Is there a way to select on the tab and
click view code again?

Sincerely,

Dave
 
Top