I have a command button which runs some code for a filter ( recorded macro).
When I Protecting Sheet It's Not Work Properly.

Sub exp1()
'
' exp1 Macro
'

'
Rows("3:3").Select
Range("C3").Activate
Selection.AutoFilter
Range("B3").Select
ActiveSheet.Range("$A$3:$P$62").AutoFilter Field:=2, Criteria1:=RGB(255, _
153, 255), Operator:=xlFilterCellColor
Range("A21").Select
End Sub

On Saturday, March 24, 2012 4:09:16 AM UTC-5, Moideen wrote:
I have a command button which runs some code for a filter ( recorded
macro).
When I Protecting Sheet It's Not Work Properly.

Sub exp1()
'
' exp1 Macro
'

'
Rows("3:3").Select
Range("C3").Activate
Selection.AutoFilter
Range("B3").Select
ActiveSheet.Range("$A$3:$P$62").AutoFilter Field:=2,
Criteria1:=RGB(255, _
153, 255), Operator:=xlFilterCellColor
Range("A21").Select
End Sub




--
Moideen'



with ActiveSheet
..EnableAutoFilter = True
..Protect Contents:=True, userInterfaceOnly:=True
..AutoFilter.Range("$A$3:$P$62").AutoFilter Field:=2, _
Criteria1:=RGB(255, 153, 255), Operator:=xlFilterCellColor
end with

If that doesn't work then
unprotect
autofilter
protect

You can't 'add' protection to a protected sheet. The
'UserInterfaceOnly' parameter does not persist between sessions and so
protect must be *reset* on all protected sheets that you want code to
be able to modify without having to toggle protection on/off every time
you need to programmatically edit cells. It's better to reset
protection at startup so it only needs doing once per session,
obviating the need for redundant processing during the rest of the
project's runtime session.

You can specify '.EnableAutoFilter' on any sheet after it's protected.
Same goes for '.EnableOutlining'.

I use a consistent methodology for protection so I can be flexible
about how protection is applied in my projects. This allows me to use
'default' protection permissions for all sheet OR sheet-specific
permissions as required per project. When using the latter I store the
protection settings in a local defined name ("uiProtect") for each
sheet where 'special' permissions are needed. This allows me the
flexibility to customize protection via my "SetupUI" routine according
to the stored settings by passing them to my "Edit_WksProtection"
routine prior to setting the target sheet's protection permissions. Of
course, this is followed by running the "Reset_WksProtection" procedure
afterwards so it's always in 'default' mode.

Here's the entire module code... (watch for line wraps)

Option Explicit

'Copy the following line into Auto_Open or Workbook_Open to initialize
the default permissions for the wkb,
'and to reset protection on all sheets at startup.
'**Note that some of the settings do not persist after the wkb is
closed, and so reset must be done each time the wkb is opened.
'================================================= =
' Call Set_DefaultProtectionOpts: Protect_AllSheets
'================================================= =

'Use enum for changing option settings so you can apply 'special'
permissions to
'specific sheets and know which permissions you're changing prior to
running WksProtect.
'Example: gvaWksProtectOpts(WksProtection.wpAllowFormatCells ) = False
'When done, run 'Set_DefaultProtectionOpts()' to restore defaults
Enum WksProtection
wpDrawingObjects = 1
wpContents = 2
wpScenarios = 3
wpUserInterfaceOnly = 4
wpAllowFiltering = 5
wpAllowFormatCols = 6
wpAllowFormatRows = 7
wpAllowFormatCells = 8
wpAllowDeleteCols = 9
wpAllowDeleteRows = 10
wpAllowInsertCols = 11
wpAllowInsertRows = 12
wpAllowInsertHLinks = 13
wpAllowPivotTables = 14
wpEnableAutoFilter = 15
wpEnableOutlining = 16
wpEnableSelection = 17 '0=xlNoRestrictions; 1=xlUnlockedCells;
2=xlNoSelection
End Enum

'Permissions Settings
'This const is to be configured to the desired default protection
permissions for a project.
'These permissions are Boolean values and so this is indicated by the
corresponding value being negative for FALSE, positive for TRUE.
'**Exception** 'wpEnableSelection' is always a positive value between
0 and 2
Const gsDEF_WKS_PROTECTION As String =
"-1,2,3,4,5,6,7,8,-9,-10,-11,-12,-13,-14,15,16,0"

Public gvaWksProtectOpts(1 To 17) 'Holds sheet protection
permission settings

Enum WkbProtection
Structure = 1: Windows = 2
End Enum

Const gsDEF_WKB_PROTECTION As String = "1,-2"
Public gvaWkbProtectOpts(1 To 2) 'Holds workbook protection
permission settings

Public Const g_szPWRD As String = " "


Sub Set_DefaultProtectionOpts()
Dim i As Integer, vProtectOpts As Variant

'Worksheet settings
vProtectOpts = Split(gsDEF_WKS_PROTECTION, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1:
gvaWksProtectOpts(i) = (vProtectOpts(i - 1) 0): Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) =
vProtectOpts(UBound(vProtectOpts))

'Workbook settings
vProtectOpts = Split(gsDEF_WKB_PROTECTION, ",")
For i = LBound(gvaWkbProtectOpts) To UBound(gvaWkbProtectOpts):
gvaWkbProtectOpts(i) = (vProtectOpts(i - 1) 0): Next
End Sub

Sub wksProtect(Optional wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const g_szPWRD as String contains the password, even
if there isn't one.
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
With wks
If Val(Application.Version) = 10 Then
.Protect Password:=g_szPWRD, _

DrawingObjects:=gvaWksProtectOpts(WksProtection.wp DrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpConten ts), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScena rios), _

UserInterfaceOnly:=gvaWksProtectOpts(WksProtection .wpUserInterfaceOnly),
_

AllowFiltering:=gvaWksProtectOpts(WksProtection.wp AllowFiltering), _

AllowFormattingColumns:=gvaWksProtectOpts(WksProte ction.wpAllowFormatCols),
_

AllowFormattingRows:=gvaWksProtectOpts(WksProtecti on.wpAllowFormatRows),
_

AllowFormattingCells:=gvaWksProtectOpts(WksProtect ion.wpAllowFormatCells),
_

AllowDeletingColumns:=gvaWksProtectOpts(WksProtect ion.wpAllowDeleteCols),
_

AllowDeletingRows:=gvaWksProtectOpts(WksProtection .wpAllowDeleteRows),
_

AllowInsertingColumns:=gvaWksProtectOpts(WksProtec tion.wpAllowInsertCols),
_

AllowInsertingRows:=gvaWksProtectOpts(WksProtectio n.wpAllowInsertRows),
_

AllowInsertingHyperlinks:=gvaWksProtectOpts(WksPro tection.wpAllowInsertHLinks),
_

AllowUsingPivotTables:=gvaWksProtectOpts(WksProtec tion.wpAllowPivotTables)
Else
.Protect Password:=g_szPWRD, _

DrawingObjects:=gvaWksProtectOpts(WksProtection.wp DrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpConten ts), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScena rios), _

UserInterfaceOnly:=gvaWksProtectOpts(WksProtection .wpUserInterfaceOnly)
End If
.EnableAutoFilter =
gvaWksProtectOpts(WksProtection.wpEnableAutoFilter )
.EnableOutlining =
gvaWksProtectOpts(WksProtection.wpEnableOutlining)

Select Case gvaWksProtectOpts(WksProtection.wpEnableSelection)
Case "0": .EnableSelection = xlNoRestrictions '0
Case "1": .EnableSelection = xlUnlockedCells '1
Case "2": .EnableSelection = xlNoSelection '-4142
End Select
End With
End Sub 'wksProtect()

Sub wksUnprotect(Optional wks As Worksheet)
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
wks.Unprotect g_szPWRD
End Sub

Sub Reset_WksProtection(Optional wks As Worksheet)
' Use at startup to re-apply non-persistent permissions
If wks Is Nothing Then Set wks = ActiveSheet
wks.Unprotect g_szPWRD
wksProtect wks
End Sub

Sub Edit_WksProtection(Settings As String)
' Use to set sheet-specific permissions
Dim i As Integer, vProtectOpts As Variant
vProtectOpts = Split(Settings, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1:
gvaWksProtectOpts(i) = (vProtectOpts(i - 1) 0): Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) =
vProtectOpts(UBound(vProtectOpts))
End Sub

Sub Protect_AllSheets()
' Use to re-apply non-persistent permissions to all sheets belonging to
ThisWorkbook
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets: Reset_WksProtection wks:
Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets()
' Use remove permissions from all sheets belonging to ThisWorkbook
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets: wksUnprotect wks: Next
Application.ScreenUpdating = True
End Sub

Sub wkbProtect(Optional Wkb As Workbook)
' Protects specified workbook.
' Assumes Public Const g_szPWRD as String contains the password, even
if there isn't one.
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
On Error Resume Next
Wkb.Protect g_szPWRD, gvaWkbProtectOpts(WkbProtection.Structure),
gvaWkbProtectOpts(WkbProtection.Windows)
End Sub

Sub Reset_WkbProtection(Optional Wkb As Workbook)
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
On Error Resume Next
Wkb.Unprotect g_szPWRD
wkbProtect Wkb
End Sub

--
Garry

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