Users on a Shared workbook

[gr8guy]

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 Tools>ShareWorkbook 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

 

[Arvi Laanemets]

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)


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 Tools>ShareWorkbook 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

 

[gr8guy]

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

=====================================