Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
allow a user to edit ranges in a protected worksheet
Hi all.
This one has been seen in the past, I run Excel 2003 and have a spreadsheet with 20 worksheets. I want each worksheet to be protected by 1: an administrator password, 2: a user password. the user password for worksheet 2 MUST NOT be able to edit any of the other worksheets and vice versa. I have followed the instructions for allowing users to edit ranges in a protected worksheet. 1. I assign specific names / permissions and complete with a password 2. Protect the sheet only allowing specific actions to be completed, e.g. unlocked cells only, use of filters and sorting with a different password 3. Protect and share the workbook with a different password However it just doesn't seem to want to work for me and when I've had someone not on the allowed list go in to edit the sheet they can enter data and save. Does anyone know what I'm doing wrong - before I become totally bald |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
allow a user to edit ranges in a protected worksheet
To start, be aware that Excel is not designed to be a secure platform.
Whatever scheme you come up with can be broken by someone with sufficient Excel knowledge. Having said that, may I make a suggestion? Set up each sheet as to locked and unlocked cells as you wish and protect it without a password. Have one blank sheet that will be visible at all times (because you can't hide all the sheets). Have another sheet that contains only 2 columns of data, the list of sheet names and the corresponding passwords you want to use. Set all the sheets to VeryHidden, except for the one blank sheet. The setting of VeryHidden precludes the casual user from unhiding that sheet because only VBA code can unhide a VeryHidden sheet. Use a Workbook_Open event macro to display an InputBox that asks the user to type in his password. The user does and the code will determine the sheet that goes with that password, and will unhide that sheet and select that sheet as the active sheet on the screen. You can also use a Workbook_BeforeClose event macro that will set all sheets (except the blank sheet) to VeryHidden when the file is closed. If multiple users will be accessing the same file without closing it, you can have a button on each sheet to close that sheet (set it to VeryHidden) when the user is through with it. Does this sound anything like what you want? HTH Otto "McA" wrote in message ... Hi all. This one has been seen in the past, I run Excel 2003 and have a spreadsheet with 20 worksheets. I want each worksheet to be protected by 1: an administrator password, 2: a user password. the user password for worksheet 2 MUST NOT be able to edit any of the other worksheets and vice versa. I have followed the instructions for allowing users to edit ranges in a protected worksheet. 1. I assign specific names / permissions and complete with a password 2. Protect the sheet only allowing specific actions to be completed, e.g. unlocked cells only, use of filters and sorting with a different password 3. Protect and share the workbook with a different password However it just doesn't seem to want to work for me and when I've had someone not on the allowed list go in to edit the sheet they can enter data and save. Does anyone know what I'm doing wrong - before I become totally bald |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
allow a user to edit ranges in a protected worksheet
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 08:55:01 -0700, McA wrote: Hi all. This one has been seen in the past, I run Excel 2003 and have a spreadsheet with 20 worksheets. I want each worksheet to be protected by 1: an administrator password, 2: a user password. the user password for worksheet 2 MUST NOT be able to edit any of the other worksheets and vice versa. I have followed the instructions for allowing users to edit ranges in a protected worksheet. 1. I assign specific names / permissions and complete with a password 2. Protect the sheet only allowing specific actions to be completed, e.g. unlocked cells only, use of filters and sorting with a different password 3. Protect and share the workbook with a different password However it just doesn't seem to want to work for me and when I've had someone not on the allowed list go in to edit the sheet they can enter data and save. Does anyone know what I'm doing wrong - before I become totally bald |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
allow a user to edit ranges in a protected worksheet
Thanks Gord and Otto, I'll give this a try and hopefully won't have any
problems with it. "Gord Dibben" wrote: 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 08:55:01 -0700, McA wrote: Hi all. This one has been seen in the past, I run Excel 2003 and have a spreadsheet with 20 worksheets. I want each worksheet to be protected by 1: an administrator password, 2: a user password. the user password for worksheet 2 MUST NOT be able to edit any of the other worksheets and vice versa. I have followed the instructions for allowing users to edit ranges in a protected worksheet. 1. I assign specific names / permissions and complete with a password 2. Protect the sheet only allowing specific actions to be completed, e.g. unlocked cells only, use of filters and sorting with a different password 3. Protect and share the workbook with a different password However it just doesn't seem to want to work for me and when I've had someone not on the allowed list go in to edit the sheet they can enter data and save. Does anyone know what I'm doing wrong - before I become totally bald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tabbing thru User-Edit Cells in Protected Sheet | Excel Discussion (Misc queries) | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
Edit Range of a protected worksheet | Excel Worksheet Functions | |||
How do I allow users to edit links in a protected worksheet? | Excel Worksheet Functions | |||
Can't Edit Contents of Protected Worksheet | Excel Discussion (Misc queries) |