Password Protection with Multiple Acceptable Passwords

[BMil8]

Hello,

I know password protection has been a frequently posted question in this forum but I'm having trouble finding what I'm looking for. All the other posts I've seen are much more detailed than what I need.

I'm looking for a way to protect a WB so only a few people can open it, but I want each user to have their own password as they will be using the same password to access other files down the road.

In other words, 1 file, 3 different users, 3 different passwords. No username is needed. I would like to give them the option to change their password after I assign them a temp one (if possible).

Thanks in advance,
Brian

 

[Ruddles]

As an alternative, could you check the username which is logged in and grant or deny access based on that? I mean, if the user has already authenticated himself to Windows, is that not sufficient to confirm his identity?

Added benefits: no extra password for the user to memorise (or write down); no need to find a secure way of storing the passwords in the workbook (or elsewhere); no need for any additional code to cope with password changes.

Environ("USERNAME") returns the username which is logged in.

 

[BMil8]

There are many different people logged on at one time, but not all of them should have access to the file.

 

[BrianExcel]

I agree with Ruddles on username. I employ that application in my workbooks when I need to protect. You can write code that says:

If environ("Username") = INSERTNAME HERE or if environ("Username") = INSERT NAME 2 HERE Then

RUN CODE

Else
End If
End sub

 

[JackDanIce]

Taking Ruddles and Brian's comments one step along, I have created a spreadsheet at work to monitor when tasks are completed by different team members.

I have a hidden worksheet (that is also password protected) which contains valid user names in column A and a security level in column B. Managers and seniors are 2 or above, everyone else 1

Using environ("Username"), my macro assess who's PC the spreadsheet is open on and then allows them restricted or unrestricted access (e.g. copy and paste is disabled, sheet is auto-saved when window is closed, lists are generated when the sheet is open to show due deadlines etc). All of this is based on environ("Username") and eliminates need to give people passwords.

I think you could employ a similar scheme and have your macro evaluate if different people are logged on, who should have access to it etc.

 

[BMil8]

OK maybe I'm confused as to how this code works. Does it promt the user to enter their username or does it just know who they are once they logged in to Windows?

If that is the case, would you be able to post the exact code I would need? The one you supplied gives an error. I apologizes but I only know basic VBA.

Thanks,
Brian

 

[JackDanIce]

Start with:

Sub TestMe()
MsgBox Environ("Username")
End Sub

And see what happens

 

[BMil8]

I replaced username with my username, but my coworkers were still able to access the file.

 

[BMil8]

We are all on the same network. Is that a problem when looking at username?

 

[JackDanIce]

No you weren't supposed to change any part of the code.
IF your username is (e.g.) "Admin" (case sensitive), try this in a new workbook:

Sub TestMe2 ()
If Environ("Username") = "Admin" Then
   MsgBox "Brian is logged into this PC"
Else
  MsgBox "You are not Brian, workbook is closing and not saving"
  ActiveWorkbook.Close Savechanges:=False
End If
End Sub

Just run the code as it is, the only part you should change is that in red