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.

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



--
Regards
Frank Kabel
Frankfurt, Germany


Steve Aletto wrote:
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.

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

--
Message posted from http://www.ExcelForum.com

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



"dragontale <" wrote:

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 advance

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

"Frank Kabel" ha scritto nel messaggio
...
Hi
just as an outline:

Many thanks.

Steve.

Thanks a lot and it is working

--
Message posted from http://www.ExcelForum.com