Sheet protection altered when saving

[Chris Ince]

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

 

[damorrison]

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

 

[Tom Ogilvy]

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