sheet protection in excel 2003 version

[Raviraja LIC]

Hi I am ravi raja. i have excel 2003 version. i have problem with my excel
file. I have 2 sheets in that file. sheet no.1 and sheet no.2.

I dont want my clients to see the contents of sheet no.2 since it contains
formulas.

i want to protect the sheet no.2 with a password. If some one click the
"sheet no.2", it should not open without a password.

Also, i came to know that this facility is available in excel 2009. I am not
able to download the excel 2009 version.

pl help. my email id is "(e-mail address removed)"


Thanks

Ravi Raja

 

[Ashish Mathur]

Dear Raviraja,

Please read up on workbook protection in Excel's Help menu.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mike H]

Hi,

Alt +F11 to open VB editor. Double click 'This workbook' and paste the code
below in. This does what you want but is totally insecure. If a user doesn't
enable macros they see your formula. Anyone with even a small amount of
knowledge and Google would view your sheet in seconds. So really my advice is
don't do it.


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet2" Then
ActiveSheet.Visible = False
response = InputBox("Enter password to view sheet")
If response = "MyPass" Then
Sheets("Sheet2").Visible = True
Application.EnableEvents = False
Sheets("sheet2").Select
Application.EnableEvents = True
End If
End If
End Sub


Mike

 

[David Biddulph]

You obviously know more about Excel 2009 than we do. Is there anything more
that you can tell us?

For Excel 2003, hide the sheet (Format/ Sheet/ Hide), then Tools/
Protection/ Protect Workbook, and add a password to protect the workbook.
Without the password you can't unhide the sheet.

 

[John]

Hi Mike
I installed your macro and when you see "sheet2" tab and clic on it,
the popup menu opens and request a password to see sheet2.
Now you type anything and the tab sheet2 disappear.
If you save the file and close it: when you reopen the file, sheet2 is
hidden and you can't reopen it because the popup menu I guess is triggered
by clicking on sheet tab2.

I deleted the macro, saved it and close the file,
When i reopen the file , sheet2 is still hidden. ( its only a test
workbook ).
It looks like a good way to hide the sheet if we can reopen it somehow.
I tried different ways ( not an expert ) but looks interesting.

Would you comment or let us know how to open sheet2
Regards
John

 

[Mike H]

John,

There was an error in it. Unhide the hidden sheet and try this version

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MySheet = "Sheet1"' Change to suit
If ActiveSheet.Name = MySheet Then
ActiveSheet.Visible = False
response = InputBox("Enter password to view sheet")
If response = "MyPass" Then
Sheets(MySheet).Visible = True
Application.EnableEvents = False
Sheets(MySheet).Select
Application.EnableEvents = True

End If
End If
Sheets(MySheet).Visible = True
End Sub


Mike

 

[John]

Hi Mike
Thank you for your reply.
You can't unhide the sheet. (Nice)
You may have stumble on something here. We know that the protection is poor
in XL but this one looks good if you can not easily find a way to reopen
sheet2
BTW your second macro works fine.
Would be interesting to know if it can be reopen.
Regards
John

 

[Gord Dibben]

John

Mike's macro did not make the sheet veryhidden, just not visible.

You should be able to unhide simply by Format>Sheet>Unhide and providing the
password.


Gord Dibben MS Excel MVP

 

[John]

Hi Gord
Your right, thanks.
I knew i was'nt very good at this, but learning.
Thanks again
Regards
John