Protecting Sheet to Prevent Viewing At All

[Guest]

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!

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[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.

 

[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

with:

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

 

[Guest]

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

 

[Guest]

Don't worry I figured it out.

Thanks anyways,

Dave