Compare Username at Workbook Open

  • Thread starter Thread starter Tim
  • Start date Start date
T

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
 
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
 
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)
 
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.
 
Dave Peterson said:
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.

Tim wrote:
>
> 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

--

Dave Peterson
Click to expand...


This code works great when i test it on my computer (WIN7/Office 2010) however when i put it on an employees computer (XP/Office 2003) the "Password to Open" box comes up. The script is not entering the password in the background for me. Is it something to do with excel converting the file to .xls instead of .xlsm?
 
Back
Top