Lock access to a worksheet depending on user name

S

Steve Aletto

Hi all,

I'd like to allow accessing an Excel worksheet only to some users. The
user should be recognized depending on his user name (typed during
Windows login), but other ways could be valid as well. Obviously macro
should be enabled, otherwise no access though the user is enabled.

Can anyone give me any suggestion? Thanks in advance,

Steve.
 
F

Frank Kabel

Hi
just as an outline:
- create wor workbook with a visible sheet that says: Macros have to be
enabled. Hide all other sheets and protext the workbook
- Use the Workbook_open event to do the following (for event procedures
see: http://www.cpearson.com/excel/events.htm):
-> check the username. E.g. use one of the functions from below:
-> if the username is valid hide worksheet 1 (the one with the
macro notice) and unhide all other sheets
-> If the username is not valid: issue a message and close the
workbook

So try something like the following:
'put the following in your workbook module:
Private Sub Workbook_Open()
dim uname
uname = XLUserName()

If lcase(uname)="myuser" then
'unhide worksheets and hide sheet 1
else
msgbox "your a re not allowed to open this workbook"
activeworkbook.close
end if

End Sub




'put the following procedures in a standard module:
-----
Public Function XLUserName() As String
XLUserName = Application.UserName
End Function

Public Function WinUserName() As String
WinUserName = Environ("UserName")
End Function
 
D

dragontale

From your reply:

'put the following procedures in a standard module:
-----
Public Function XLUserName() As String
XLUserName = Application.UserName
End Function

Public Function WinUserName() As String
WinUserName = Environ("UserName")
End Function


---
I have tried this and find out that the second function is not used
And the username is not the login name of the window.

And when I replace the XLUserName = Application.UserName to
XLUserName = WinUserName , I got a compile error:

"Can't find project or Library"

Anything I did wrong here, Please help. Thanks in advanc
 
D

Dave Peterson

Put these in a general module. They work together to get the logon name.



Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
 
Top