Defining users in Excel 1

[Rogy]

I have Excel document, which has 5 worksheets.
The data in each of this sheet will be entered by 5
different users. Each user has his own sheet.
This file is shared on the network.

Each of this user should have his own username and based
on this username, only his worksheet must be shown.

Is this possible to create?
(I have Excel 97)

Thanks in advance

 

[DannyLondon]

Hi Rogy

The function Environ("UserName&quot will return a user's network login name.

Using this, I would start with a workbook that had all of the worksheets hidden and the workbook protected by a password that you do not disclose.

In the Workbook_Open() event, put in code to read the user name, unprotect the workbook, unhide one of the worksheets and protect the workbook again.

The advantage of having all of the sheets hidden at the start is that if the user is prompted to enable/disable macros when the workbook is opened, choosing to disable them will leave all sheets hidden rather than all sheets visible.

Danny...

 

[xlbo]

Good idea - environ("username&quot only works with NT tho - you can get the username if you are not on NT via an aPI call - let me know if you need this.
Other than that danny's idea is sound

You would need something like:

Private sub Workbook_Open()
dim myUser as string
application.screenupdating = false
myUser = environ("username&quot
select case myUser
case user1 'change to user logon
mSheet = "Sheet1"
case user2
mSheet = "Sheet2"
case user3 'etc etc

case else
mSheet = ""
msgbox "You are not authorised to look at this file"
end select

For each ws in thisworkbook.worksheets
if ws.name = mSheet then
ws.visible = true
else
ws.visible = false
end if
next
application.screenupdating = true
end sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Rogy]

Geoff your idea is very good!

But, I am runing aplication on Win98.
So Environ function doesn't work. (I've tried it).

Could you help with that API call, you mentioned?

Thanks
Rogy

 

[xlbo]

Here's the API call
This goes in a STANDARD module

Public myUser As String
Public ChangeType As String
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Sub Get_User_Name()
Dim lpBuff As String * 25
Dim ret As Long
ret = GetUserName(lpBuff, 25)
myUser = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Sub

The code I posted last time would be changed to:

Private sub Workbook_Open()

application.screenupdating = false
Get_User_Name
select case myUser
case user1 'change to user logon
mSheet = "Sheet1"
case user2
mSheet = "Sheet2"
case user3 'etc etc

case else
mSheet = ""
msgbox "You are not authorised to look at this file"
end select

For each ws in thisworkbook.worksheets
if ws.name = mSheet then
ws.visible = true
else
ws.visible = false
end if
next
application.screenupdating = true
end sub
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Rogy]

Thanks again!
Now it works fine!

Just one question:
How can I allow to the user to see more than
one sheet?
I tried with:

case user1
mSheet = "Sheet1,Sheet2"

...but it didn't work.
Any ideas?

Rogy

 

[xlbo]

Slight change required - bit more clunky - requires hardcoding the sheets to hide / unhide

Private sub Workbook_Open()

application.screenupdating = false
for each ws in thisworkbook.worksheets
ws.visible = false
next

Get_User_Name

select case myUser
case user1 'change to user logon
sheets("Sheet1&quot.visible = true
sheets("Sheet2&quot.visible = true
case user2
sheets("Sheet2&quot.visible = true
case user3 'etc etc
sheets("sheet3&quot.visible = true
case else
msgbox "You are not authorised to look at this file"
end select
application.screenupdating = true
end sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[DannyLondon]

As a post script, Environ("UserName&quot works in Windows 2000.