Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blocking/Disabling selected worksheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blocking/Disabling selected worksheet
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 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
curing the blocking of cells. | Excel Discussion (Misc queries) | |||
blocking cells | Excel Discussion (Misc queries) | |||
printing got numbering blocking | New Users to Excel | |||
blocking cells | Excel Worksheet Functions | |||
Blocking specific cells | Excel Worksheet Functions |