Blocking/Disabling selected worksheet

[DJ Ghale]

I created an excel file for payroll purpose with each employee's pay info
broke down in separate worksheets. Is there a way i can disable or hide other
worksheets so that each employee can only view their own worksheet? (maybe
set a password protection?) I have the option to save each worksheet in a
different workbook and send it as an email attachment but i don't want to
waste time sending 20 different emails with attachments if there's an easier
way to do this. Thanks in advance.

 

[Gord Dibben]

Just posted this a few minutes ago to another user.

Further to Otto's sugestion here is a sample of what can be done with no
passwords on individual sheets.

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

Naturally you want all this code inviisble to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties
and "Lock project for viewing"

Enter a unique password.

BUT don't forget Otto's warning that Excel's security is weak, but cracking
VBA Project passwords is more difficult than cracking sheet protection
passwords.


Gord Dibben MS Excel MVP

On Mon, 15 Sep 2008 14:16:01 -0700, DJ Ghale <DJ