Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protection altered when saving
Hi,
I am trying to set up three worksheets each with slightly different functions that a group of end-users will have access to. The sheets all need to be protected, but sheet 2 and 3 need to allow access to input data. Both Sheets 2 and 3 need the users to be able to insert hyperlinks and sheet 3 needs outlining enabled, even when protected. I have incuded the macros below. They all seem to work fine as long as when you save the workbook, you are in sheet 3. If not, when the workbook is openned up again and you switch to sheet 3, enabling is turned off on the protected sheet and then I scream!! Private Sub Workbook_Open3() With Worksheets("Sheet1") ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End With End Sub Private Sub Workbook_Open2() With Worksheets("Sheet2") ActiveSheet.Protect Password:="arachnid", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingHyperlinks:=True End With End Sub Private Sub Workbook_Open() With Worksheets("Sheet3") ActiveSheet.Protect Password:="arachnid", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingHyperlinks:=True, UserInterfaceOnly:=True .EnableOutlining = True End With End Sub Any help VERY very gratefully received. I am assuming it's something simple but... Thanks in advance Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protection altered when saving
just a start,
select the cells that need data, right click on mouse,select format cells, select protection tab, then uncheck the hide box, now when the sheet is protected, you will still be able to input data in the selected cells, you can also have unprotect sheet in your macro Hope this gives you a start |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet protection altered when saving
Why do you say
with Worksheets("Sheet3") Activesheet. Get rid of the Activesheet or activate the sheet you want Private Sub Workbook_Open3() With Worksheets("Sheet1") .Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub Private Sub Workbook_Open2() With Worksheets("Sheet2") .Protect Password:="arachnid", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ AllowInsertingHyperlinks:=True End With End Sub Private Sub Workbook_Open() With Worksheets("Sheet3") .Protect Password:="arachnid", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ AllowInsertingHyperlinks:=True, _ UserInterfaceOnly:=True .EnableOutlining = True End With End Sub -- Regards, Tom Ogilvy "Chris Ince" wrote in message ... Hi, I am trying to set up three worksheets each with slightly different functions that a group of end-users will have access to. The sheets all need to be protected, but sheet 2 and 3 need to allow access to input data. Both Sheets 2 and 3 need the users to be able to insert hyperlinks and sheet 3 needs outlining enabled, even when protected. I have incuded the macros below. They all seem to work fine as long as when you save the workbook, you are in sheet 3. If not, when the workbook is openned up again and you switch to sheet 3, enabling is turned off on the protected sheet and then I scream!! Private Sub Workbook_Open3() With Worksheets("Sheet1") ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End With End Sub Private Sub Workbook_Open2() With Worksheets("Sheet2") ActiveSheet.Protect Password:="arachnid", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingHyperlinks:=True End With End Sub Private Sub Workbook_Open() With Worksheets("Sheet3") ActiveSheet.Protect Password:="arachnid", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingHyperlinks:=True, UserInterfaceOnly:=True .EnableOutlining = True End With End Sub Any help VERY very gratefully received. I am assuming it's something simple but... Thanks in advance Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
Losing Cell Protection When Saving Workbook | Excel Discussion (Misc queries) | |||
Protection & saving editable file | Excel Discussion (Misc queries) | |||
Excel 2003 changes protection when saving | Excel Programming |