Excel Hide / Unhide some worksheets with password

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

LVL 2
Mandy_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
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.

Regards,
-Glenn
EE-ShowHideSheets.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mandy_Author Commented:
Wow!Thank you so much. Well done
0
Glenn RayExcel VBA DeveloperCommented:
I think you broke my personal record for fastest acceptance of an answer!  I'm glad you think this will help.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.