Share via

how to password protect a single tab in a workbook in excel 2010

Anonymous
2012-04-20T19:01:39+00:00

I would like to be able to password protect a single tab within a workbook. I need the information on this one tab to not be able to be seen unless you have the necessary password to get into it. Is this possible?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-04-20T20:50:42+00:00

    I would like to be able to password protect a single tab within a workbook. I need the information on this one tab to not be able to be seen unless you have the necessary password to get into it. Is this possible?

    Hi,

    There are several ways to this but you need to bear in mind that they will only deter the honest. Anyone with the will to do so can bypass any protection you put in place very quickly indeed so in short if you let anyone open this workbook and they are intent on seeing this sheet then they can. However, here's the method I use.

    ALT+F11 to open VB editor. Double click 'ThisWorkbook' and paste the code below in on the right. Change the name at the start of the code to the correct one and close VB editor. The password is currently set to a case sensitive MyPass

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim MySheets As String, Response As String

    MySheet = "Sheet1"

    If ActiveSheet.Name = MySheet Then

    ActiveSheet.Visible = False

        Response = InputBox("Enter password to view sheet")

            If Response = "MyPass" Then

                Sheets(MySheet).Visible = True

                Application.EnableEvents = False

                Sheets(MySheet).Select

                Application.EnableEvents = True

            End If

    End If

    Sheets(MySheet).Visible = True

    End Sub

    200+ people found this answer helpful.
    0 comments

15 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-02-21T18:07:42+00:00

    Mike H, how will the code change if i want to password protect 2 tabs?

    9 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-04-20T19:22:39+00:00

    You can't quite do what you want without a bit of code.

    Create a macro in a regular code module and paste in the code, modifying the name and password as desired.

    Sub OpenWS()

        Dim S As String

        S = InputBox("Enter Password")

        If S = vbNullString Then

            Exit Sub

        ElseIf S <> "your password" Then

            Exit Sub

        Else

            Worksheets("HiddenSheet").Visible = xlSheetVisible

        End If

    End Sub

    You can attach this to a button or menu item for quick access. It will make worksheet HiddenSheet visible if the correct password. If the passwords do not match, the HiddenSheet remains hidden.

    Then, in the ThisWorkbook code module, use

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

        ThisWorkbook.Worksheets("HiddenSheet").Visible = xlSheetVeryHidden

    End Sub

    This wil make the HiddenSheet VeryHidden upon closing the workbook. xlVeryHidden prevents the workbook from being listed in the list of open workbooks, simply Visible would allow. Without going into the code, the user has no idea that the sheet even exists.

    Of course, as secuity in Excel is very weak, a person with a conversational knowledge of VBA could break through this code rather easily. As I tell all my clients, Excel security is present to prevent the honest user from making an honest mistake. It isn't good enough to hide proprietary data and code or other intellectual property.

    7 people found this answer helpful.
    0 comments
  3. Anonymous
    2017-01-11T05:45:32+00:00

    I'm trying to use this for multiple tabs and getting error, "Compile error: Ambiguous name detected: Workbook_SheetActivate" I've tried changing the names to other things and still getting the error. Do you have any ideas on how to fix? Thanks!

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2012-04-20T19:23:02+00:00

    Hi,

    Press Alt+F11 to go to VBA Window. | in the Project Window click your Sheet you want to be hidden | below is there’s Properties window for your sheet | Select “2-Very Hidden” in the Visible option | return to VBA Project window, right click your Sheet then select VBAProject Properties | in the protection tab check the Lock project for viewing | Then set your project

    Hope it helps,

    ~jaeson

    6 people found this answer helpful.
    0 comments