Excel with protection based on Windows user name

[Tim Ng]

I'm thinking of setting below protection for my Excel file so only designated users could open and use it.

The concept is that I'll put a list of Windows user name in the file (in a hidden sheet) which lists out who can open the file.
Then when the file is being opened, it will lookup the Windows user folder location and see any folder name as per Windows user name in hidden sheet is available.
If not, it would either cannot be opened or cleared all contents in the Excel file immediately.

This is a bit complex (or impossible) that I've no idea how to accomplish that...
Anybody could help in this case?

 

[Kyle123]

Honestly this is pretty insecure and easily bypassed - you'd be better off requiring a password to open or putting the workbook on a shared drive only those with permission have access to

 

[mse330]

You should be able to achieve what you need with the below code. However, as Kyle123 said, this can be easily bypassed with anyone who have little experience in excel or with some google search

Private Sub Workbook_Open()

Dim Check As Boolean, CheckUser As String, PCUser As String, i As Long

Check = False
PCUser = Environ("UserName")

For i = 1 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' you need to change the sheet & range based on your file
CheckUser = Sheet1.Range("A" & i).Value
    If PCUser = CheckUser Then
        Check = True
        Exit Sub
    End If
Next i

MsgBox "You don't have permission to access this file", vbCritical
ActiveWorkbook.Close SaveChanges:=False

End Sub

 

[Tim Ng]

Hi Kyle and MSE330, many thanks for sharing and this really works for me.
Regarding to the suggestion - since the file needs to be put in users' computer to use with, it is not possible to put it in restricted share drive.
Also the use of password protect is not that workable as users may share the password to unauthorized users easily.
Anyway I'm quite sure users would not be able to jailbreak the protection code suggested by MSE330 based on my understanding...

 

[Kyle123]

Really? All they need do is disable macros