Sat 08 Aug 2015

Tips & Tricks 104 - Hide a Sheet Securely

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only few fields which are non-sensitive in nature. Then, you can resort to below trick -

1. Save your sheet as macro enabled i.e. with .xlsm extension.
2. ALT+F11 to open VBA window (This can also be opened by taking right click in the tab and choosing View Code)
3. Locate your workbook name in Project Explore window which is in top left corner. If this is not available, you can either do CTRL+R or View > Project Explorer
4. Click your sheet.
5. You have Sheet Properties window below Project Explorer. If not available, do F4 or View > Properties Window
6. Last option in Properties Window is Visible, which you should set to 2- xlSheetVeryHidden


7. Go back to your workbook / sheet in Project Explorer. Right Click and Insert Module. - This is an important step and many people generally forget about this step.
8. Tools > VBA Project Properties > Protection tab - Check Lock Project for Viewing and put passwords.


Now, your sheet can be unhidden only by those persons who know the password for your VBA project.

Sat 23 Aug 2014

Tips & Tricks 45 - Lock Cells Containing Formulas

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. CTRL+A to select entire Sheet or select top left corner between row 1 and column A.
2. Right Click > Format Cells > Protection = Click Locked Check box to remove tick mark from Locked to make all cells unlocked.


3. CTRL + G and click on Special.


4. Click on Formulas and OK


5. It will highlight all the cells which contain formulas. Right Click one of the highlighted cell > Format Cells > Protection = Click Check box to tick mark Locked to make all selected cells locked.

6. Now protect the sheet from Review tab.