Excel Password protection on .xlsx - hiding a worksheet

Posted on 2014-08-24
Last Modified: 2014-08-24

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.

Question by:pau1scott
    LVL 8

    Expert Comment

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


    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", _

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

    '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", _

    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.
    LVL 26

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now