Hi,

Can anybody help me with this?

i have a shared workbook in which there are some sheets with names of Users as Sheet names. What i want to happen is that any user who logs in to the workbook, will be able to view the sheet with his name reflecting as the sheet name. the rest of the Sheets of other Users logged in to that same workbook will be hidden from him & vice versa for other Users.

I tried using the following code, but not working. the First User who logged in, his sheet is visible on all users desktop on the network. Probably because the ToolsShareWorkbook option shows that all users below logged in & the IF statement reads True for the 1st person who logs in.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
If Application.UserName = "aju" Then
ActiveSheet.Range("a1:a65536").Rows.Hidden = False
For Each Sh In Sheets
If Not Sh.Name = "Eijaz" Then
Sh.Visible = False
End If
Next Sh

ElseIf Application.UserName = "aireen" Then
For Each Sh In Sheets
ActiveSheet.Range("a1:a65536").Rows.Hidden = False
If Not Sh.Name = "Aireen" Then
Sh.Visible = False
End If
Next Sh

ElseIf Application.UserName = "zaki" Then
ActiveSheet.Range("a1:a65536").Rows.Hidden = False
For Each Sh In Sheets
If Not Sh.Name = "Zaki" Then
Sh.Visible = False
End If
Next Sh

' THIS IS FOR THE SUPERVISOR WHO WILL BE ABLE TO SEE ALL THE USER SHEETS ON HIS/HER 'COMPUTER DESK.
ElseIf Application.UserName = "nairg" Then
ActiveSheet.Range("a1:a65536").Rows.Hidden = False
For Each Sh In Sheets
Sh.Visible = True
Next Sh

Else
' IF SOME USER OUTSIDE THE TEAM TRIES TO OPEN THE FILE, WONT BE ABLE TO VIEW THE FILE. FILE WILL CLOSE.
For Each Sh In Sheets
ActiveSheet.Range("a1:a65536").Rows.Hidden = True
Sh.Visible = True
Next Sh
MsgBox "Access Denied for Outside Teams!"
ThisWorkbook.Close savechanges:=False
Application.ScreenUpdating = True

End If

End Sub

Please help!

Rgds,

Eijaz