Does anyone know of any way you can password protect a sheet in a workbook? The Excel version is Excel 2007. I'm not looking to password protect a whole workbook with a password. I'm visioning VBA code for this.

Thanks for any help you can provide.

Does this help: http://office.microsoft.com/en-us/help/password-protect-worksheet-or-workbook-elements-HP010078580.aspx

JR

JanR,

Thanks for the quick response.

sorry, I should have made myself a little clearer. I would like it so when the person clicks on the tab for sheet1 in the workbook it prompts you for a password before it opens it.

Is that a little clearer?

- Charles

If you go into the VBA and place this code on the sheet you want protected NOT A MACRO it should do what your asking


Private Sub Worksheet_Activate()
Dim MYpassword As String
MYpassword = InputBox("EnterPassword")

If MYpassword = "123" Then
'
Else
Sheets("Sheet1").Select
End If
End Sub

Thank You Access Guy. I'll try it tomorrow in work. I would try it now but my computer only came with Excel starter version.:(

- Charles

Access Guy

It doesn't quite do what I want.

If you move the password box you can see the data under it. Can you make it so the spreadsheet cannot be seen until the password is entered?

- Charles

hmmm....

Private Sub Worksheet_Activate()
Dim MYpassword As String
Sheets("Sheet1").Select 'here We Move to Sheet1 (Or whatever sheet you want, just change the name)

MYpassword = InputBox("EnterPassword")

If MYpassword = "123" Then
Sheets("Sheet2").Select 'Put the name of your protected sheet here
Else
Sheets("Sheet1").Select
End If
End Sub


Hope that works for your needs

The code works but when excel opens you can cancel the password on sheet one and just click on sheet 2 which defeats the purpose of the password. How can sheet 2 be hidden until a password is put in correctly? I have 2003 and 2010.

You need to have the code in sheet 2, not sheet 1. This way sheet 1 is always visible but sheet 2 will be useless without the password.
(See image for location of where to paste code)

Now you have to do is move the password prompt to view the data. Can all data be hidden until password is correct?

I attatched an example of an updated code version. (Wasn't working quite how I wanted it to)
But I am not able to recreate your problem of being able to simply move the password prompt in order to view data on the second tab.
When the password prompt comes up, sheet 1 is in the background, not sheet 2.

The assumption is that sheet1 can be viewed by anyone, and sheet2 it to be protected. Correct?

When i open your attachment it opens in sheet 2 and i can see sheet two, if i click on sheet 1 then on sheet 2 i dont see anything until i put the password in.

hmmm interesting... it doesn't open in sheet 2 for me. I tried it in 2010 and 2003...
umm.
hmmm I dunno what to tell ya. Does it always open in sheet 2? even if you go to sheet 1 and save it then close and re-open?

That did it. Thanks for the details.:) Always the simple things that get ya..