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:
- Set up a list of the computer aliases that are allowed to view your data in a tab on your spreadsheet
- 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.
Sub AuthUser() | |
Dim Cell As Range | |
Dim person As String | |
Dim authperson As String | |
person = Application.UserName | |
'---------------------------------------- | |
'written by Angelina Teneva, 2013 | |
'----------------------------------------- | |
For Each Cell In ActiveWorkbook.Worksheets("Names").Range("E1:E100") | |
authperson = Cell.Value | |
If person = authperson Then | |
Worksheets("data").Visible = xlSheetVisible | |
Else | |
Worksheets("data").Visible = xlSheetVeryHidden | |
MsgBox ("Sorry, you are not authorized to view this data") | |
End If | |
Exit For | |
Next Cell | |
End Sub |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ShowUser() | |
ActiveCell.Value = Environ("UserName") 'returns the domain name | |
ActiveCell.Offset(1, 0).Value = Application.UserName 'the user registered name | |
ActiveCell.Offset(2, 0).Value = Application.UserLibraryPath 'location where COM-Addins are installed | |
ActiveCell.Offset(3, 0).Value = "C:\Users\" & UCase(Environ("UserName")) & "\Desktop" 'returns a custom directory | |
ActiveCell.Offset(4, 0).Value = ActiveWorkbook.FullName 'the path + the name of the file <– assimes file has been saved | |
ActiveCell.Offset(5, 0).Value = ActiveWorkbook.name 'returns the name of the file alone | |
ActiveCell.Offset(6, 0).Value = ActiveWorkbook.path 'returns the path where it is saved | |
ActiveCell.Offset(7, 0).Value = Application.PathSeparator 'returns the dash | |
'hp | |
'Angelina | |
'C:\Users\hp\AppData\Roaming\Microsoft\AddIns\ | |
'C:\Users\HP\Desktop | |
'C:\Users\hp\Google Drive\Analytics\Blogging\Excel Spreadsheets\Get it Right in Excel Workshop.xlsb | |
'Get it Right in Excel Workshop.xlsb | |
'C:\Users\hp\Google Drive\Analytics\Blogging\Excel Spreadsheets | |
'\ | |
End Sub |
P.S. Don’t forget to hide and protect the worksheet in which you put your authorized names