Password protect only one tab inside a spreadsheet?

[mrvegas]

I have a budget spreadsheet with about a dozen tabs in it. I want to password protect the "payroll" tab, but not any of the other tabs.

Is this possible?

 

[Smitty]

Nothing's stopping you; you can protect just one worksheet.

If you're worried about people seeing that sheet, then hide it, just note that it can be unhidden. To prevent that you need to use VBA, but it's very easy.

 

[jproffer]

Look for this:

then you'll see this:

 

[mrvegas]

Nothing's stopping you; you can protect just one worksheet.

If you're worried about people seeing that sheet, then hide it, just note that it can be unhidden. To prevent that you need to use VBA, but it's very easy.

Smitty, you are correct, I want to keep it in the workbook but I don't want others to be able to see it, except me, when I need to make changes like adding new employees or salary increases.

I'm quite unskilled when it comes to VBA, but maybe you can talk me through it?

 

[Smitty]

Open the VBE (VB Editor) with ALT+F11. Then goto Insert-->Module.

In the new window that opens on the right you can add the following:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> HidePayroll()<br>    Sheets("Payroll").Visible = xlVeryHidden<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UnHidePayroll()<br>    Sheets("Payroll").Visible = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You can run the code with F5. The Private keyword means that the code can't be run from the Macro list in Excel, so you'll have to be in VBA to run it. When you do this, make sure that you protect the VBA Project (Tools-->Options-->Protection-->Lock Project for Viewing). The next time you go to the VBE you'll need the password to get in.

You can also write code that evalutes the user name; if it's you then show the sheet, but leave it hidden for everyone else.

 

[mrvegas]

Open the VBE (VB Editor) with ALT+F11. Then goto Insert-->Module.

In the new window that opens on the right you can add the following:

Private Sub HidePayroll()
Sheets("Payroll").Visible = xlVeryHidden
End Sub

Private Sub UnHidePayroll()
Sheets("Payroll").Visible = True
End Sub


You can run the code with F5. The Private keyword means that the code can't be run from the Macro list in Excel, so you'll have to be in VBA to run it. When you do this, make sure that you protect the VBA Project (Tools-->Options-->Protection-->Lock Project for Viewing). The next time you go to the VBE you'll need the password to get in.

You can also write code that evalutes the user name; if it's you then show the sheet, but leave it hidden for everyone else.

Thanks a million! I will try it first thing tomorrow.

 

[MARK858]

You might want to try the ready made file attached. I can't take credit for the code as I acquired it a long time ago.
The administrator password is set as "secret" and the general password is set as "everyone".
Obviously as Smitty said remember to set the project protection

When you do this, make sure that you protect the VBA Project (Tools-->Options-->Protection-->Lock Project for Viewing)

http://www.box.com/s/51abf9cb67a980045d1e

 

[mrvegas]

Thanks a million! I will try it first thing tomorrow.

OK, I have a little problem. When I click on Tools-->Options, I do not have a "Protection" choice available to me. Does this mean that my 2007 Excel does not have the proper installation of add-ons or something? My choices under options are: editor; editor format; general; docking.

 

[jproffer]

Try right clicking on the project in the Project Explorer, VBAProject Properties...could be in there.

 

[mrvegas]

that did it, thanks!