Is there a way I can password protect a worksheet in a workbook from opening?
Not from being edited but from opening. I have a workbook which contains some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to any
help on this.

Thanks,
Lauren

Lauren, here is one way, be aware that sheet protection can be overcome
very easily with code you can find by doing a search on Google

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.

You also need to protect the VBA Project to keep someone form seeing the
password there

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
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. Be aware that this
password can be broken by third party software

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



"laurenm" wrote in message
...
Is there a way I can password protect a worksheet in a workbook from
opening?
Not from being edited but from opening. I have a workbook which contains
some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to
any
help on this.

Thanks,
Lauren

FormatSheetHide

ToolsProtectionProtect Workbook.

Set a password and OK with "Structure" and "Windows" checked.

Note: Excel's internal(worksheet and workbook) protection is not very secure and
many password crackers abound on the Internet.

I suggest you stick with the two separate workbooks.

If the data is confidential, don't include it.


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 13:54:02 -0800, laurenm
wrote:

Is there a way I can password protect a worksheet in a workbook from opening?
Not from being edited but from opening. I have a workbook which contains some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to any
help on this.

Thanks,
Lauren

Gord Dibben MS Excel MVP

Thanks so much for your quick and thorough response! I will give it a try.

All the best,
LaurenM

"Paul B" wrote:

Lauren, here is one way, be aware that sheet protection can be overcome
very easily with code you can find by doing a search on Google

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.

You also need to protect the VBA Project to keep someone form seeing the
password there

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
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. Be aware that this
password can be broken by third party software

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



"laurenm" wrote in message
...
Is there a way I can password protect a worksheet in a workbook from
opening?
Not from being edited but from opening. I have a workbook which contains
some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to
any
help on this.

Thanks,
Lauren

Hi Gord

I'm trying to do the same thing as Laura. I like the simplicity of your
reply (I'm not a programmer...) but in this case one would still have to
provide the password to the workbook for anyone else to see it, and once open
anyone can go to a cell that contains linked information from the hidden
sheet, then FormatSheetUnhide and the hidden information is now available
to all!

Is there a way to password-protect the hidden sheet itself, so that it can't
be unlocked and viewed even if the workbook is open?

Thanks

Jay

"Gord Dibben" wrote:

FormatSheetHide

ToolsProtectionProtect Workbook.

Set a password and OK with "Structure" and "Windows" checked.

Note: Excel's internal(worksheet and workbook) protection is not very secure and
many password crackers abound on the Internet.

I suggest you stick with the two separate workbooks.

If the data is confidential, don't include it.


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 13:54:02 -0800, laurenm
wrote:

Is there a way I can password protect a worksheet in a workbook from opening?
Not from being edited but from opening. I have a workbook which contains some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to any
help on this.

Thanks,
Lauren

Gord Dibben MS Excel MVP

Jay

If you protect the workbook structure after hiding the sheet you would need a
password to unprotect the workbook before unhiding the sheet.

But from your question I get the idea that you don't quite understand what
workbook protection means.

When I say "protect the workbook" I don't mean having a password to open the
workbook.

Look at ToolsProtectionProtect Workbook.

When that is enabled, FormatSheetUnhide becomes unavailable.


Gord

On Wed, 17 Jan 2007 20:20:00 -0800, Jay wrote:

Hi Gord

I'm trying to do the same thing as Laura. I like the simplicity of your
reply (I'm not a programmer...) but in this case one would still have to
provide the password to the workbook for anyone else to see it, and once open
anyone can go to a cell that contains linked information from the hidden
sheet, then FormatSheetUnhide and the hidden information is now available
to all!

Is there a way to password-protect the hidden sheet itself, so that it can't
be unlocked and viewed even if the workbook is open?

Thanks

Jay

"Gord Dibben" wrote:

FormatSheetHide

ToolsProtectionProtect Workbook.

Set a password and OK with "Structure" and "Windows" checked.

Note: Excel's internal(worksheet and workbook) protection is not very secure and
many password crackers abound on the Internet.

I suggest you stick with the two separate workbooks.

If the data is confidential, don't include it.


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 13:54:02 -0800, laurenm
wrote:

Is there a way I can password protect a worksheet in a workbook from opening?
Not from being edited but from opening. I have a workbook which contains some
sheets with public information and one or two sheets with confidential
information. I would love to be able to password protect the confidential
worksheets from opening while the other sheets can be left alone and be
viewed as needed. Currently I am creating and distributing two separate
workbooks to our users; one with the confidential sheets and the other
without them. We are working with Excel 2002 and 2003. I look forward to any
help on this.

Thanks,
Lauren

Gord Dibben MS Excel MVP