Is it possible to lock a tab from being viewed?

[Marq]

Using Excel 2010:

Can I password protect a tab in a workbook (with multiple tabs) from being viewed?

I have been asked to include a worksheet from an excel file on my pc, into an existing workbook on the company network. But they want this particular worksheet passworkprotected from even being viewed unles syou have the password.

So I'm thinking I can password protect a tab in a workbook so when you go to choose it you have to plug in a password to be able to view it.

I know I can passwrod protect the worksheet but thats not what they are wanting...they want it to be viewed only by certain people YET they must have this worksheet in this particular workbook....i suggested the obvious which was "why not have this as a stand alone on the net work?..why must it be incorporated into the an existing workbook then protected???....doesnt matter..thats how they want it.

ANyway..can this be done...I havent found anything yet..Im still searching.

 

[Smitty]

You can easily hide a worksheet (Cells-->Format-->Hide), but anyone can unhide it.

A more secure way is to use the VBA VeryHidden property to unhide a sheet and tie that to code to prompt for a password. Here's an example:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> HideSheet()<br>    Sheets("Sheet1").Visible = xlVeryHidden<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> UnHide()<br>    <SPAN style="color:#00007F">Dim</SPAN> pword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>        pword = InputBox("Please enter a password to unhide Sheet1", "Password Required")<br>            <br>            <SPAN style="color:#00007F">If</SPAN> pword <> "password" <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "Sorry, wrong password", vbCritical + vbOKOnly, "Access denied"<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                Sheets("Sheet1").Visible = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Marq]

You can easily hide a worksheet (Cells-->Format-->Hide), but anyone can unhide it.

A more secure way is to use the VBA VeryHidden property to unhide a sheet and tie that to code to prompt for a password. Here's an example:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> HideSheet()<br>****Sheets("Sheet1").Visible = xlVeryHidden<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> UnHide()<br>****<SPAN style="color:#00007F">Dim</SPAN> pword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>****<br>********pword = InputBox("Please enter a password to unhide Sheet1", "Password Required")<br>************<br>************<SPAN style="color:#00007F">If</SPAN> pword <> "password" <SPAN style="color:#00007F">Then</SPAN><br>****************MsgBox "Sorry, wrong password", vbCritical + vbOKOnly, "Access denied"<br>****************<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>************<SPAN style="color:#00007F">Else</SPAN><br>****************Sheets("Sheet1").Visible = <SPAN style="color:#00007F">True</SPAN><br>************<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>************<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

Can't seem to get it working. I did find something online that instructs the following:

right click on the tab you want to hide (sheet2).
click on "view code"
while in VBA chose the worksheet you want at the top...then chose 2 SheetVeryHidden
then right click again on the sheet of choice and click VBAP Object Properties...
under protection tab check "Lock"
then below type in a password.

I did all of this and it did hide the sheet2...but when I went into VBA to unhide it, it let me do so without prompting to type in a password.

so then I tried your idea and it didnt hide or unhide anything...I copied and pasted your suggestion...changed all the sheet1's to sheet2's and nothing happened.

 

[Smitty]

You didn't password protect the sheet, but the VBA project, which means that no one can open up the VBE to unhide the sheet.

As for the code, did you put it in a General Module (Insert-->Module)? Once you change the code you actually need to run the first one (you can do that with F5). The second you can link to a button on a sheet.