|
Users on a Shared workbook
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 |
Users on a Shared workbook
Hi
Give for every user his own (1-scheet password-protected) workbook. For supervisor, create a workbook, which has a sheet for every user, where this users data are displayed through links. All are happy, and anybody can open his/her workbook at any time - no messages about being the workbook in use by another user anymore. -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "gr8guy" wrote in message ... 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 |
Users on a Shared workbook
hi arvi,
i cant have different workbooks for different agents. and also want to have the desired workbook coded in VBA, with sheets for every agent & To limit Users using shared workbook to their own worksheets! rgds, eijaz ===================================== "Arvi Laanemets" wrote in message ... Hi Give for every user his own (1-scheet password-protected) workbook. For supervisor, create a workbook, which has a sheet for every user, where this users data are displayed through links. All are happy, and anybody can open his/her workbook at any time - no messages about being the workbook in use by another user anymore. -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "gr8guy" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com