Logon to Worksheet
Michael,
as I would really like them create the impression of security.
Just keep in mind that it is only an "impression". To an experienced
user, any kind of protection scheme that can be devised is merely
a minor annoyance.
Anyway....
Here's what I do on one of my workbooks.
There are other ways.
Set up a one column range on a separate sheet and name the range
"Auth_List". In that range, place the Usernames (or serials as you have
them).
Ina regular module:
Sub RestrictCheck()
Dim Auth As Range ' Set memvar "Auth" as a range
Set Auth = Range("AuthList") 'Set "Auth" to the "AuthList" range
On Error GoTo ErrorHandler ' Error trap in case name isn't in
AuthList
Dim IsOnList As String ' memvar necessary for next function
' Check to see if this UserName matches a UserName in the AuthList
IsOnList = Application.WorksheetFunction. _
VLookup(Application.UserName, Auth, 1, False)
' The above line will cause an error if a match is not found.
' Insure that error trapping is on, so that the error can be
generated
On Error GoTo 0 ' Reset error trapping
' User found on list. Do nothing
Exit Sub
ErrorHandler:
If Left(Err.Description, 9) = "Unable to" Then
'User not on list
End If
End Sub
In the above, in place of 'User not on List you can use
ThisWorkbook.Saved = True
ThisWorrkbook.Close.
To make this work when the workbook opens, call this sub from
the Workbook_Open Event or from a sub named Auto_Open
Example:
Sub Auto_Open()
RestrictCheck
End Sub
John
Michael wrote:
Hi John,
Yes they do have own copies. The username however is a
serial no. instead of an actual name as we use this for
logging PC's. So I was thinking maybe VLOOKUP table may
work with code ?, as I would really like them create the
impression of security.
Michael
-----Original Message-----
Michael,
Does everyone have their own copy of Excel on their PC's??
i.e. Do they each have unique Usernames??
You can check by going into Tools/Options/General
If they do, an easy way would be to just check the
Application.Username against a list and if on the list
allow them to open the file.
Post back with more info and I'll try to supply you with
some
code based on your situation.
John
Michael wrote:
Hi Guys, Is it possible to create a log-on input box
with
excel that will search a column of names before it will
allow access to a worksheet, maybe with a auto_open
macro.
I am trying to avoid passwork protection and I only
allow
certain members of staff access file, with giving
everybody a password.
Thanks for your help
Michael
.
|