asked on

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

Leo

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.

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial