Unprotect Sheet for the duration of a script

[magmike]

I need to have my sheet protected, but the following script does not work if it is. Is there a way to unprotect the sheet at the beginning of the script, and reprotect it the end?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:B2")) Is Nothing _
Then Exit Sub
Dim myRange As Range

'Set the range of your table
Set myRange = Range("A4:E100")
If Target <> "" Then
myRange.AutoFilter field:=Target.Column, Criteria1:=Target
Else
myRange.AutoFilter field:=Target.Column, Criteria1:="<>"
End If
End Sub

Thanks in advance for your help,
magmike

 

[isabelle]

hi magmike,

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly)

ActiveSheet.Unprotect "MyPassWord"

ActiveSheet.Protect "MyPassWord"

isabelle

Le 2013-09-06 23:11, magmike a écrit :

I need to have my sheet protected, but the following script does not work if it is.

Is there a way to unprotect the sheet at the beginning of the script,
and reprotect it the end?

 

[GS]

I need to have my sheet protected, but the following script does not

work if it is. Is there a way to unprotect the sheet at the beginning
of the script, and reprotect it the end?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:B2")) Is Nothing _
Then Exit Sub
Dim myRange As Range

'Set the range of your table
Set myRange = Range("A4:E100")
If Target <> "" Then
myRange.AutoFilter field:=Target.Column, Criteria1:=Target
Else
myRange.AutoFilter field:=Target.Column, Criteria1:="<>"
End If
End Sub

Thanks in advance for your help,
magmike

There's no reason to toggle protection when you reset protection when
the file is first opened. Make sure you set 'UserInterfaceOnly:=True'
so your code can modify the sheet but the user can't via the UI. Make
sure your code includes 'AllowFiltering:=True' OR set
'worksheet.EnableAutoFilter:=True'...

Dim wks As Variant
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect <password>
wks.Protect Password:=<password>, UserInterfaceOnly:=True, _
AllowFiltering:=True '//add more as req'd
Next 'wks

It's feasible, however, that not all sheets need/are protected in a
file so you may want to use a string constant of delimited sheetnames
that protection is applied to...

Const sSheetsToProtect$ = "Sheet1,Sheet2,Sheet4"
Dim vName As Variant
For Each wks In Split(sSheetsToProtect$, ",")
Sheets(vName).Unprotect <password>
Sheets(vName).Protect Password:=<password>, _
UserInterfaceOnly:=True, _
AllowFiltering:=True '//add more as req'd
Next 'wks

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[magmike]

I need to have my sheet protected, but the following script does not workif it is. Is there a way to unprotect the sheet at the beginning of the script, and reprotect it the end? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A2:B2")) Is Nothing _ Then Exit Sub Dim myRange As Range 'Set the range of your table Set myRange = Range("A4:E100") If Target <> "" Then myRange.AutoFilter field:=Target.Column, Criteria1:=Target Else myRange.AutoFilter field:=Target.Column, Criteria1:="<>" End If End Sub Thanks in advance for your help, magmike

Garry, where would I put your snippet - inside my script? In a module?

 

[magmike]

I need to have my sheet protected, but the following script does not workif it is. Is there a way to unprotect the sheet at the beginning of the script, and reprotect it the end? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A2:B2")) Is Nothing _ Then Exit Sub Dim myRange As Range 'Set the range of your table Set myRange = Range("A4:E100") If Target <> "" Then myRange.AutoFilter field:=Target.Column, Criteria1:=Target Else myRange.AutoFilter field:=Target.Column, Criteria1:="<>" End If End Sub Thanks in advance for your help, magmike

I'm not sure what to do with the line that begins "expression.Protect..." but the other worked just fine like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:B2")) Is Nothing _
Then Exit Sub
Dim myRange As Range
ActiveSheet.Unprotect "ThankYouIsabelle"
'Set the range of your table
Set myRange = Range("A4:E100")
If Target <> "" Then
myRange.AutoFilter field:=Target.Column, Criteria1:=Target
Else
myRange.AutoFilter field:=Target.Column, Criteria1:="<>"
End If
ActiveSheet.Protect "ThankYouIsabelle"
End Sub

 

[isabelle]

magmike it means that you have the choice of what you want to protect

ActiveSheet.Protect Password:="zz", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

isabelle

Le 2013-09-07 01:50, magmike a écrit :

 

[GS]

Garry, where would I put your snippet - inside my script? In a
module?

Given that it should be run when the workbook first opens.., it should
go in ThisWorkbook module in Workbook_Open event OR in a standard
module as a stand-alone reusable procedure that can be called from the
Workbook_Open event or an Autpen sub...

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect <password>
wks.Protect Password:=<password>, UserInterfaceOnly:=True, _
AllowFiltering:=True '//add more as req'd
End Sub

...and use it like this...

Const sSheetsToProtect$ = "Sheet1,Sheet2,Sheet4"
Dim vName As Variant
For Each wks In Split(sSheetsToProtect$, ",")
ResetProtection Sheets(vName)
Next 'wks

...from ThisWorkbook.Workbook_Open (not recommended) or
mOpenClose.Autpen (recommended), where mOpenClose is a standard
module that you can use to store Sub Autpen(), global defs for
variables/constants, Sub InitGlobals(), and Sub Auto_Close(). The Auto_
macros are legacy routines and should replace Workbook_Open and
Workbook_BeforeClose. (I only use ThisWorkbook class for runtime use of
workbook events, never for startup/shutdown)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops! Copy/paste error

..and use it like this...

Const sSheetsToProtect$ = "Sheet1,Sheet2,Sheet4"
Dim vName As Variant

For Each vName In Split(sSheetsToProtect$, ",")

ResetProtection Sheets(vName)

Next 'vName

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion