Worksheet Protection

A

Angeline

Hello,
Is it possible to password protect a worksheet from actually opening up? I
have a series of worksheets one of which should only be opened up with a
password. I can password protect the cells but I was hoping to protect the
worksheet from displaying unless a password is entered.

Hope someone can help.

Angeline
 
G

galimi

Angeline,

You can do this with some code. You can create a userform in VBA that has a
password protected text box which can appear through a button on your sheet
or on a menu that will prompt the client for a password when the sheet should
be shown.
 
A

Angeline

Galimi,
Thank you for reply. I do not know how to write VBA. Could you let me know
how to create a user from in VBA and what the code I would need to execute
your suggestion?

Thanks!
Angeline
 
P

Paul B

Angeline, here is one way, you will also need to password protect your VBA
project so the password can't be seen there.
But be aware that protection in Excel is very weak and can be overcome very
easy.

Private Sub Worksheet_Activate()
'password protect your VBA project
Dim strPassword As String
On Error Resume Next
Const Password = "123" '**Change password here**

Me.Protect Password:=Password
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to view this sheet", "Password
required !")

If strPassword = "" Then
Me.Previous.Select
Exit Sub
ElseIf strPassword <> Password Then
MsgBox "Password Incorrect", , "Wrong password"
Me.Previous.Select
Exit Sub
Else
Me.Unprotect Password:=Password
Me.Columns.Hidden = False
End If

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub



To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

To change the security settings go to tools, macro, security, security level
and set it to medium



To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
select VBA project properties, protection, check lock project for viewing
and set a password. Press Alt and Q to close this window and go back to your
workbook and save and close the file.

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Top