Protect / un protect macro options

[nparsons75]

Hi,

I use the following code in a spreadsheet. Within the code it unprotected the worksheet and then protects it once it has run the macro. I need to, when protecting again, ensure that the use of auto filter is permissible. For some reason, each time it protects, my auto filters will not work so I have to manually unprotect and re-protect with filters selected as ok. Defeating the object of auto operating this function within the code. Would anyone know if this is possible? Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)Dim bBold As Boolean




If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next




    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With




    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
    With Sheet2
        .Unprotect Password:="OEESTAT"
        If .Range("A1") = vbNullString Then
            .Range("A1:E1") = Array("CELL CHANGED", _
                    "OLD VALUE", _
            "NEW VALUE", "TIME OF CHANGE", "DATE OF Change ")
        End If




        With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
        .Value = Target.Address
        .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
            .ClearComments
            .AddComment.Text Text:= _
                "David Bailey:" & Chr(10) & "" & _
                            Chr(10) & _
                "Bold values are the results of formulas "
              End If
            .Value = Target
            .Font.Bold = bBold
            End With




        .Offset(0, 3) = Time
        .Offset(0, 4) = Date
         .Offset(0, 5) = Application.UserName
        End With
        '.Cells.Columns.AutoFit
        .Protect Password:="OEESTAT"
    End With
    vOldVal = vbNullString




    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With




On Error GoTo 0
End Sub

 

[Scott T]

Easiest thing to do would be to record a macro of you protecting the sheet and checking the use AutoFilters from the Allow all users of this worksheet to box. This should give you the code need.