Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To limit Users using shared workbook to their own worksheets!
Can anybody help me with this? How can you limit Users using shared workbook to their own worksheets, so that one User (Username say, "aju") will only be able to see his/her worksheet(say sheet name, "Eijaz") & not be able to view any other Users Worksheet. Each Users viewing their own worksheets! 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 only the sheet on which his name reflects 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Errors with 5-10 users in Shared Workbook | Excel Worksheet Functions | |||
Limit similtaneous users of workbook | Excel Discussion (Misc queries) | |||
conflicting users in shared worksheets. | Excel Discussion (Misc queries) | |||
how to limit edit access to certain users for a shared workbook? | Excel Worksheet Functions | |||
Users on a Shared workbook | Excel Programming |