Protect Your Data without any Codes… VERY Hidden is Much More Than Just Hidden

You can Download the Exercise File  by Clicking on the Button

 

 

 

The most valuable thing in your Excel Files is the data you create and store.

sometimes, if it is viewed without authorization, the outcome can be devastating.

You might be storing personal information about employees or clients, or financial records. Or usernames and Passwords for different sites. May be credit cards, and bank account information. In all case, identity theft is a real possibility.

In this article I show you one of the simple techniques you can put in place to keep your data from being breached.

Protection is like insurance: when everything goes according to plan, you feel justified for not having bothered with it. However, when disaster strikes, you wish you’d have been more prepared.

So, let’s see how we can hide valuable information from being viewed in Excel.

Because our intention is to hide some of the worksheets having sensitive data, from prying eyes, then will do that using 3 techniques: Basic, Intermediate and Powerful.

In our exercise file, we have a worksheet storing Employee information, a second one storing Clients information, then we have 3 other worksheets with sensitive data: Bank accounts, online login information then email accounts with Username and password.

How can I prevent anyone from viewing the contents of these worksheets whether intentionally or unintentionally?

Basic Technique

Hide the sheets only.

You can select any sheet then right-click on the sheet tab and select Hide.

However, anyone with basic knowledge of Excel, can Right-Click on any other sheet and select Unhide ►Then select the sheet to unhide ► OK

And that will bring back the sheet. So this technique is a weak technique since it can be reversed.

Intermediate Technique

Hide Sheets and Protect Workbook.

This method complements the previous one to prevent reversing the visibility of any sheet.

After Hiding one or multiple sheets ► Click on the Review Tab ► Click on Protect workbook

Type a password then confirm the password ►OK

Advantage of this technique:

No one without the password can unhide the worksheet. The Unhide command is not even active.

Disadvantage of this techniques:

  1. We cannot access the regular functionality for the other worksheets, like Inserting, deleting, Renaming, Copying, Change Tab Color…
  2. By switching to the visual Basic Editor (ALT + F11) anyone can know that there are some sheets hidden.

Let’s provide the correct password and unhide the worksheets.

Therefore, I have a preference to the third technique, which will be done in the visual basic editor without any coding.

Powerful technique:

In this technique, we’ll be hiding 3 sheets in the visual basic editor.

To switch to the visual basic editor, we use the shortcut ALT + F11

To the left side we see: The Project Explorer window and the Properties window. If they are not displayed, we can select them from the View menu. Alternatively, you can use the shortcuts CTRL + R (for the Project Explorer) and F4 (for the Properties window).

In the Project Explorer Select the name of the sheet you want to hide.

Then go to the last option in the Properties window.

Click on the drop list for the visible property: You have three options (XLSheetVisible, XLSheetHidden, XLSheetVeryHidden) ► Select XLSheetVeryHidden.

This option can not be reversed from Excel. So if we hit ALT + F11 to go back to Excel, then right click on a sheet Tab ► as if the Very Hidden Worksheet, does not exist.

However, it can be changed back to XlSheetVisible from the properties window in the visual Basic editor.

To avoid that, we need to password protect the VBA project. But because protection is intended to protect codes► we need to insert a blank module for the protection to stick. Otherwise it will disappear automatically.

So, to switch back to the VBE, we hit ALT + F11

Click on the “Insert” menu and select ► Module

We do not need to write anything in that Module but that’s the only way to guarantee that protection does not go away.

To prepare for applying protection, Close the Properties Window, then click on the Collapse symbol for the project.

Right Click on the Project name in the project Explorer ► Select VBAProject Properties… ► The VBA Properties window opens.

In the VBA Project Properties dialog box ► Click on the Protection Tab

Check the box: Lock project for viewing

Type a Password

Confirm the Password

Hit OK

The Project will be protected ONLY after closing the VBE, Saving the Excel File and closing the Excel File.

Reopen the Excel File

Switch to the VBE (ALT + F11)

Try to expand the VBA Project (by Clicking on the + sign) ► You need to provide the Password

Now your Very Hidden Sheets are protected from Viewing 😊

Watch the Tutorial Here

 

 

 

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>