Shared workbook

[Steveqae]

I have a spreadsheet that is shared between 8 people on a network. Is it
possible to protect this so that each person only sees the parts of the
worksheet that is relevant to them, but have the whole thing available to
the administrator?

 

[Gord Dibben]

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible.

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP