none
Password Protect Multiple Excel Tabs RRS feed

  • Question

  • I have an Excel Document with multiple 15+ tabs and I need to password protect most of them so that users can't even see what's in them unless they provide a password. I found a code solution on the net, but this works only for one tab. How can I make it work for multiple tabs? Thank you in advance! Here's the code:

    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



    • Edited by terreror Monday, February 25, 2019 1:26 PM
    Monday, February 25, 2019 1:26 PM

Answers

  • Hi terreor,

    I don't know why you cannot download my shared file....
    I've changed my workbook a little: added sheets to 15, and modify code to hide sheets with odd numbers (1, 3, 5,... 15).

    This screenshot shows a case of typing invalid password. (visible sheets with even numbers: 2, 4, 6...14)

    You only need to copy this code into "ThisWorkbook".
    Private Sub Workbook_Open()
        ' --- make all sheets visible
        Dim xlSheet As Worksheet
        For Each xlSheet In ThisWorkbook.Worksheets
            xlSheet.Visible = True
        Next
        Dim typedPassword As String
        ' --- input Password
        typedPassword = Application.InputBox("Input Password", "Prompt")
        ' --- Password check: "mypassword" is a valid password
        If (typedPassword = "mypassword") Then
            ' --- valid
            MsgBox "Thanks. You're verified."
        Else
            ' --- not valid
            MsgBox "Password is not valid. You have some restrictions."
            ' --- make sheet 1, 3, 5, 7 hidden
            Worksheets(1).Visible = xlVeryHidden
            Worksheets(3).Visible = xlVeryHidden
            Worksheets(5).Visible = xlVeryHidden
            Worksheets(7).Visible = xlVeryHidden
            Worksheets(9).Visible = xlVeryHidden
            Worksheets(11).Visible = xlVeryHidden
            Worksheets(13).Visible = xlVeryHidden
            Worksheets(15).Visible = xlVeryHidden
        End If
    End Sub
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • Marked as answer by terreror Thursday, February 28, 2019 8:58 AM
    Wednesday, February 27, 2019 10:21 PM

All replies

  • Hi terreror,

    I suppose it is a better an easier way to check password when a book is opened.
    You can write this code in "ThisWorkbook", instead of Sheet and Module.
    Private Sub Workbook_Open()
        ' --- make all sheets visible
        Dim xlSheet As Worksheet
        For Each xlSheet In ThisWorkbook.Worksheets
            xlSheet.Visible = True
        Next
        Dim typedPassword As String
        ' --- input Password
        typedPassword = Application.InputBox("Input Password", "Prompt")
        ' --- Password check: "mypassword" is a valid password
        If (typedPassword = "mypassword") Then
            ' --- valid
            MsgBox "Thanks. You're verified."
        Else
            ' --- not valid
            MsgBox "Password is not valid. You have some restrictions."
            ' --- make sheet 1, 3, 5, 7 hidden
            Worksheets(1).Visible = xlVeryHidden
            Worksheets(3).Visible = xlVeryHidden
            Worksheets(5).Visible = xlVeryHidden
            Worksheets(7).Visible = xlVeryHidden
        End If
    End Sub

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, February 26, 2019 1:03 AM
  • Hi and thank you for that, but unfortunately didn't work with me. I'm trying to password protect about 15+ tabs and leave just 2 open for the public.
    Tuesday, February 26, 2019 9:35 AM
  • Hi terreror,

    The number of tabs (worksheets) does not matter. I suppose my sample code would be the simplest.
    Could you provide your latest code?

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, February 26, 2019 9:50 AM
  • I haven't changed anything to the code I gave as an example in my request. To ask you, where the number of the worksheets are:

            ' --- make sheet 1, 3, 5, 7 hidden
            Worksheets
    (1).Visible = xlVeryHidden
            Worksheets
    (3).Visible = xlVeryHidden
            Worksheets
    (5).Visible = xlVeryHidden
            Worksheets
    (7).Visible = xlVeryHidden

    Am I supposed to change the digits with the actual names of the tabs, as it is not allowing me, neither it works with the digits?

    Thank you!

    Tuesday, February 26, 2019 2:17 PM
  • Hi terreror, good morning from Japan,

    I'm afraid you miss some code in your VBA.

    I've shared my sample "Password Protect Multiple Sheets.xlsm" via OneDrive.
    Please download and check how it works.
    (valid password is "mypassword")

    Digit in Worksheets() indicates a number of a worksheet, and it begins from 1 in left-to-right order with each sheet. You can specify sheet-name like Worksheets("Sheet1"), instead. 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Wednesday, February 27, 2019 12:13 AM
  • Hi Ashidacchi and thank you! I can't download and open certain links on my computer, can you please paste the code in here? Thank you!
    Wednesday, February 27, 2019 12:26 PM
  • Hi terreor,

    I don't know why you cannot download my shared file....
    I've changed my workbook a little: added sheets to 15, and modify code to hide sheets with odd numbers (1, 3, 5,... 15).

    This screenshot shows a case of typing invalid password. (visible sheets with even numbers: 2, 4, 6...14)

    You only need to copy this code into "ThisWorkbook".
    Private Sub Workbook_Open()
        ' --- make all sheets visible
        Dim xlSheet As Worksheet
        For Each xlSheet In ThisWorkbook.Worksheets
            xlSheet.Visible = True
        Next
        Dim typedPassword As String
        ' --- input Password
        typedPassword = Application.InputBox("Input Password", "Prompt")
        ' --- Password check: "mypassword" is a valid password
        If (typedPassword = "mypassword") Then
            ' --- valid
            MsgBox "Thanks. You're verified."
        Else
            ' --- not valid
            MsgBox "Password is not valid. You have some restrictions."
            ' --- make sheet 1, 3, 5, 7 hidden
            Worksheets(1).Visible = xlVeryHidden
            Worksheets(3).Visible = xlVeryHidden
            Worksheets(5).Visible = xlVeryHidden
            Worksheets(7).Visible = xlVeryHidden
            Worksheets(9).Visible = xlVeryHidden
            Worksheets(11).Visible = xlVeryHidden
            Worksheets(13).Visible = xlVeryHidden
            Worksheets(15).Visible = xlVeryHidden
        End If
    End Sub
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • Marked as answer by terreror Thursday, February 28, 2019 8:58 AM
    Wednesday, February 27, 2019 10:21 PM
  • Hi Ashidacchi and thank you for your support, it is very much appreciated!

    Kind Regards!

    Thursday, February 28, 2019 8:58 AM