Excel Password protection on .xlsx - hiding a worksheet

Hi,

We are distributing an Excel .xlsx file with password protected worksheets (formulae and calculations must be invisible to the users).  
Also we would like to hide some of the worksheets, so that they can't be un-hidden.  I know about the "xlVeryHidden" option in the VBA editor, but anyone who knows just a little of Excel can get in and change this back without a password.

So the question is can a non-macro worksheet be hidden with password protection?  Also I get the feeling the Excel protection isn't that amazing and that anyone determined to get in and see the spreadsheet details wouldn't have that much trouble.

Thanks
pau1scottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LeoCommented:
yes it can be hidden, have you tried this procedure?

UserInterFaceOnly

The UserInterFaceOnly is an optional argument of the Protect Method that we can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that protected with or without a password.

You need to be fully ware that, If you use the Protect method with the UserInterfaceOnly argument set to True on a Worksheet, then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To set the user UserInterfaceOnly back to True after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

The solution to this is quite easy and requires us to make use of the Workbook_Open Event (fired as soon as the Workbook is opened) As this is an Event of the Workbook Object (ThisWorkbook) we must place the code as shown below in the Private Module of ThisWorkbook. To get here easily, right click on the Excel icon, top left next to "File" and select "View Code"

Private Sub Workbook_Open()

'If you have different passwords

  'for each Worksheet.

      Sheets(1).Protect Password:="Secret", _
    UserInterFaceOnly:=True

      Sheets(2).Protect Password:="Carrot", _
     UserInterFaceOnly:=True

'Repeat as needed.

End Sub

The above code is good if each Worksheet you need to have your macros operate on have different Passwords or your do not want to Protect all Worksheets. We can set the UserInterfaceOnly to True without having to un-protect first.

If you want to set the UserInterfaceOnly to True on all Worksheets and they have the same password you can use this code which must be placed in the same place as the above code.

Private Sub Workbook_Open()

Dim wSheet As Worksheet
For Each wSheet In Worksheets

            wSheet.Protect Password:="Secret", _
            UserInterFaceOnly:=True

Next wSheet

End Sub

Now, each time you open the Workbook, the code will run and set the UserInterfaceOnly to True allowing your macros to operate while still prevent any changes from users.
0
MacroShadowCommented:
Excel protection is a lot less than amazing. It is so easy to crack, and no you can't hide a sheet in a way that it cant be unhidden.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.