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

 

[BrianExcel]

To add to Jack's code if you want multiple names...

Sub TestMe2 ()
If Environ("Username") = "Admin" or if Environ("Username") = "Admin2" 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

 

[BMil8]

So I get the correct message box when running the code. How can I apply this so it runs when somebody tries to open the workbook?

 

[BrianExcel]

When you are in the VBA window (ALT + F11) there is a 'ThisWorkbook' title under the 'Microsoft Excel Objects.'

If you put in this sheet under the 'Workbook_Open()' title then it will run whenever the workbook opens. So the routine (including the code below) would look like this:

Private Sub Workbook_Open()
If Environ("Username") = "Admin" or if Environ("Username") = "Admin2" 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

 

[BMil8]

This works great! One final questions (I hope): What if the user does not have macros enabled? I tested this code with a coworker and they were able to open the file even though I did not include their username. It turns out they did not have macros enabled. Is there a way around this other than having every body change their macro settings?

Thanks again.

 

[BrianExcel]

Thats a tough one. You can either have them set their Macros to 'Auto' (Dangerous, because viruses can be transmitted this way).

You can also use 'Trusted Locations' within the Excel > Excel Options > Trust Center > Trusted Locations menu.

Having them save and open the document from the location you specify should register in the trust settings and open without worrying about Macros.