Excel Hide / Unhide some worksheets with password

Posted on 2014-08-16
Last Modified: 2014-08-16
Dear experts,

i like for example 6 sheets viewable and 3 sheets hide / unhide with password.
I've found the code below and execute the macros with e.g.  ctrl.u for unhide and CTRL-H for hide.

1. I want to change that  i can unhide / hide  sheet7,sheet8,sheet9 in one step with CTRL-U, CTRL-H
2.Now every User can see the Macro Code with the password include - How can i protect that?

Thanks in advance

;hide sheet
Sub HideSheet2()
Sheets("Sheet2").Visible = xlVeryHidden
End Sub

;unhide sheet
Sub ViewSheet2()
pword = Application.InputBox("Enter password to view sheet2", "PASSWORD REQUIRED")
If pword = "PASSWORD" Then 
Sheets("Sheet2").Visible = True
End If
End Sub

Open in new window

Question by:Mandy_
    LVL 27

    Accepted Solution

    To answer your first question:
    1) You'll create two macros to hide and show the three sheets, like in these examples:
    Sub Hide_789()
        Dim sht As Variant
        For Each sht In Array("Sheet7", "Sheet8", "Sheet9")
            Sheets(sht).Visible = xlVeryHidden
        Next sht
    End Sub
    Sub Show_789()
        Dim pword As String
        Dim sht As Variant
        pword = Application.InputBox("Enter password to view Sheets 7,8,9", "Password Required")
        If pword = "PASSWORD" Then
            For Each sht In Array("Sheet7", "Sheet8", "Sheet9")
                Sheets(sht).Visible = True
            Next sht
        End If
    End Sub

    Open in new window

    2) Assign shortcut keys to these two macros through the "View Macros" dialog box.
        a) Open the dialog by pressing [Alt]+[F8]
        b) Click on the macro to assign, say "Hide_789", then click the "Options" button
        c) Enter the letter to activate the macro...
    ***** IMPORTANT NOTICE:   The built-in shortcuts [Ctrl]+U (underline) and [Ctrl]+H (find/replace) will be superceded if you just enter the letter "u" or "h".  Instead, enter the uppercase letters [Shift]+u or [Shift]+h.  This will keep the original Excel shortcuts in place. *****
        See this example:
    assign shortcut - using SHIFT    d) Repeat steps b) and c) for the other macro, again, using the [Shift] key to distinguish the letter.

    Second quesion:  How to protect your code?
    Well, no Excel workbook is completely safe from being cracked, whether it's sheet passwords, file passwords, or even VBA passwords like I'm about to show.  But it is sufficiently strong enough to stop most users.  To protect your VBAProject code:
    1) Open VBA
    2) While viewing your code (VBAProject), click Menu item "Tools", then "VBA Project Properties...".  You'll see a new window.
    VBAProject Properties3) Click the "Protection" tab.
    4) Click the check box for "Lock project for viewing"
    5) Enter a password to view the file and enter again to confirm.
    Lock Project and password-protect6) Save and close your file.

    When you re-open your file, open VBA again.  You'll see your file/VBAProject listed in the project explorer window on the left, but collapsed so that you can't see any code.  Double-click the filename; you'll be prompted for a password to open the file.  Enter the password and you can see your code.
    Unlock VBAproject
    Of course, choose a different password than used for unhiding the sheets!

    I've attached an example workbook with all this implemented so you can test.

    LVL 2

    Author Closing Comment

    Wow!Thank you so much. Well done
    LVL 27

    Expert Comment

    by:Glenn Ray
    I think you broke my personal record for fastest acceptance of an answer!  I'm glad you think this will help.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now