At times you need the user to not be able to unhide the sheet that you hide in excel. But if you use the regular right click hide option, anyone can unhide your sheet (if your workbook is not protected)

Let me share with you a trick using which the user won’t be able to easily unhide the sheet

 

The problem with hiding the sheet

Anyone can right click and unhide your sheet. The way to get around this is to protect your workbook structure

  1. Go to Review Tab
  2. Click on Protect Workbook (Structure option should be ticked)
  3. The password is optional

Protect Workbook (which is not full proof) option will give you an additional layer of security that no one is able to unhide your sheet unless they unprotect the workbook (and know your password). But protecting workbook also locks out adding, moving, deleting, hiding worksheets and renaming worksheets.

 

Trick to Hide the Sheet (that’s not easy to unhide) without Protecting Workbook

Normally when you right click on the sheet name you see options to Hide, Unhide but the 3rd option – Very Hidden is available only in the VBA window. Let’s take a look

  1. Right Click on the sheet to Hide
  2. Click on View Code
  3. In the VBA window roll down to sheet properties
  4. In the Visible drop down choose Very Hidden
  5. This will ensure that the unhide option is greyed out when someone tries to unhide sheet in Excel

Be aware that a smart user can figure this out, to make this more secure you need to restrict the ability to access the VBA window for this file. Here is how it’s done

  • If your excel doesn’t already have a VBA module, please insert one by clicking on the Insert menu in the VBA window. Insert Tab >> Module
  • Don’t worry we won’t be writing any code, this is just an empty module
  • Now go to Tools Tab >> VBA Project Properties
  • Under Protection >> Put in a password and enable “Lock project for viewing”
  • To put this into effect, save the file in .xlsm format and restart it
  • Now when the user tries to access the VBA window for reverting the sheet back to visible, she’ll be prompted for the password

Although this is hard to break but a determined user can break in, there are several password breaking tools available.

 

A few other interesting posts

  1. Hide / Unhide Excel Window
  2. Hide Options in Excel
  3. Hide / Display Data Labels with a Click
  4. Unhiding Multiple Sheets at Once

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI