How Can I validate if a user is authorized to view certain data or not?

I'm in

25th May is approaching fast and so is GDPR. For those of you who are not yet aware – GDPR is a EU-wide regulation on the handling of privacy data

A potential implication for all data analysts is that they may have to take extra precautions as to who can access the data they provide for further analysis within the company.

Of course, you can add password protection to your workbook but:

a) this does not prevent unauthorized people from finding out your password

b) it’s easily hack-able

A clever way in which you can add an extra protection layer would be to make sure that your spreadsheet always checks that the person trying to open a spreadsheet is actually authorized to view it.

Here are a few simple steps in which you can implement this:

  1. Set up a list of the computer aliases that are allowed to view your data in a tab on your spreadsheet
  2. Add the following code as an “upon open” event in your workbook

(Get it Right in Excel Workshop)

Thus, the code will trigger every time the workbook is opened, ensuring that the “data” tab is hidden if the person opening the workbook is not authorized to view it.

As you’ll see above, the code makes use of the “Application.UserName” property

Below you can find the the most common approaches in which you can check the name, path or both of a given workbook. These can be pretty handy if you need your code to be opening and closing various workbooks

P.S. Don’t forget to hide and protect the worksheet in which you put your authorized names

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.