Excel

Hide or unhide worksheets

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

Ken Puls

Description:

This macro demonstrates how to unhide a worksheet, and also how to hide it so that it can only be unhidden by a macro. The macro to unhide the sheet also requests a password that is not case sensitive. The name of the worksheet to hide/unhide is specified in the code, so most users will never even know it exists at all. 

Discussion:

There are often times when you may want confidential data or calculations hidden from certain users. This macro hides the worksheet using Excel's xlSheetVeryHidden constant, which can only be set back to visible by using a macro. This will prevent most users from seeing the contents of the sheet. The macro prompts the user to enter a password when they ask to unhide the sheet as well, and it has been specifically coded to make the password non case sensitive. If you would like to make the password case sensitive, remove the line "Option Compare Text" from the standard module code. The code is also used to hide the Confidential worksheet when the workbook is opened, so that it will be hidden even if a user unhides it and forgets to rehide it before they close the workbook (providing that the file is reopened with macros enabled). NOTE: We also suggest locking the VBA Project for viewing (instructions included) to stop an informed user from exposing the password in the VBE. 

Code:

instructions for use

			

'** This code goes in a standard module ** Option Explicit Option Compare Text 'Password to unhide sheets Const pWord = "MyPassword" Sub HideSheets() 'Set worksheet to Very Hidden so that it can only be unhidden by a macro Worksheets("Confidential").Visible = xlSheetVeryHidden End Sub Sub ShowSheets() 'Prompt the user for a password and unhide the worksheet if correct Select Case InputBox("Please enter the password to unhide the sheet", _ "Enter Password") Case Is = pWord With Worksheets("Confidential") .Visible = xlSheetVisible .Activate .Range("A1").Select End With Case Else MsgBox "Sorry, that password is incorrect!", _ vbCritical + vbOKOnly, "You are not authorized!" End Select End Sub '** This code goes in the ThisWorkbook module ** Option Explicit Private Sub Workbook_Open() 'Turn off screen updates Application.ScreenUpdating = False 'Hide confidential sheet at startup Call HideSheets 'Activate cell A1 on the Dashboard sheet at startup With Worksheets("Dashboard") .Activate .Range("A1").Select End With 'Restore screen updates Application.ScreenUpdating = True End Sub

How to use:

  1. Rename a worksheet in your workbook to "Dashboard".
  2. Put a message on the page saying "Welcome to the dashboard".
  3. Rename a worksheet in your workbook to "Confidential".
  4. Put a message on the page saying "Get out!".
  5. Copy above code for the standard module.
  6. In Excel press Alt + F11 to enter the VBE.
  7. Press Ctrl + R to show the Project Explorer.
  8. Right-click desired file on left (in bold).
  9. Choose Insert -> Module.
  10. Paste code into the right pane.
  11. Copy the code for the ThisWorkbook module.
  12. In the project explorer, locate the ThisWorkbook object.
  13. Double click the ThisWorkbook object.
  14. Paste code into the right pane.
  15. From the Tools menu, choose VBA Project Properties, and click the Protection tab.
  16. Check the "Lock Project For Viewing" checkbox, and enter and confirm a password you will remember.
  17. Press Alt + Q to close the VBE.
  18. Save workbook before any other changes.
  19. Close and reopen the workbook.
 

Test the code:

  1. You should be taken to cell A1 on the Dashboard worksheet.
  2. From the main Excel interface, press Alt + F8 to open the macro dialog box.
  3. Choose ShowSheets and click Run.
  4. Enter "MyPassword" (no quotes) when prompted.
  5. You should be taken to call A1 on the Confidential worksheet.
  6. Close the workbook WITH saving changes. (So that the confidential worksheet is saved as visible.)
  7. Reopen the workbook.
  8. The Confidential worksheet should be hidden again.
 

Sample File:

HideUnhideWorksheets.zip 10.44KB 

Approved by mdmackillop


This entry has been viewed 482 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express