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

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.

	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

view raw ForEachCell_AuthUser.bas hosted with ❤ by GitHub

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

	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

view raw

ShowWbkPaths.bas

hosted with ❤ by GitHub

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