[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12457
  • Last Modified:

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

0
Mandy_
Asked:
Mandy_
  • 2
1 Solution
 
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now