Compare Username at Workbook Open

[Tim]

I wish to create a small security trap whereby a user cannot access a
file unless his/her username is within a named range. I know Excel
security is basic, but thats the level I'm at.

So if the user is not named in a named range a dialog box appears "No
access" etc and the file closes. The username I will compare against
the username on the xl 2007 panel. If the username matches open the
file as normal

 

[Mike H]

Hi,

I don't know anything about the xl2007 panel but this checks the user
against a range on sheet 1 and closes the workbook if the active user doesn't
appear there. As you state yourself protection in Excel including this
solution is flimsy

Private Sub Workbook_Open()
Dim c As Range
Dim ThisUser As String
ThisUser = Environ("Username")
For Each c In Sheets("Sheet1").Range("A1:a10")
If c.Value = ThisUser Then
GoTo getmeout
End If
Next
MsgBox "Not Authorised"
ActiveWorkbook.Close False
getmeout:
End Sub

Mike

 

[Chip Pearson]

In the ThisWorkbook code module, paste the following code:

Private Sub Workbook_Open()
Dim UserName As String
Dim V As Variant
UserName = Environ("username")
On Error Resume Next
V = Application.Match(UserName, Range("ValidUserNames"), 0)
If IsError(V) = True Then
Debug.Print "Unauthorized user: " & UserName
'ThisWorkbook.Close savechanges:=False
Else '
Debug.Print "Valid Username: " & UserName
' valid user name
End If
End Sub

Uncomment the ThisWorkbook.Close line to close the workbook if the
username is not in the list of user names in the defined name
"ValidUserNames".

Note that these user names are the Windows logon user names, not the
User Name option that appears in Excel's Options dialog.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

You may want to consider using a helper workbook that checks the credentials of
the user and if that's ok, it would open the real workbook.

If you give that real workbook a nice password, you could include the password
in the code in the helper workbook (and protect that workbook's project).

I'd create the helper workbook with two sheets. One that has instructions to
the user and one that has the list of valid users. Then I'd hide that second
sheet. It won't stop the very interested, but it would stop most(???).

I based my validation on Chip's post:

Option Explicit
Private Sub Workbook_Open()

Dim UserName As String
Dim V As Variant
dim myPWD as string
dim wkbk as workbook

UserName = Environ("username")

myPWD = "hi"

On Error Resume Next
V = Application.Match(UserName,
thisworkbook.worksheets("UserNames").range("a:a"), 0)
On Error Goto 0

If IsError(V) = True Then
Msgbox "You're not authorized!"
Else
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
Password:=myPWD)

wkbk.RunAutoMacros which:=xlAutoOpen
end if

ThisWorkbook.Close savechanges:=False

End Sub

The users would always have to open this helper workbook first. If they
disabled macros, they'd see your instruction page. If they weren't authorized,
they'd see the msgbox.

If they enabled macros and were authorized, the helper workbook would open the
real workbook and then the helper workbook would close itself.