How to Stop the Macro from Disabling the Sort and Filter Functions?

[bssol]

I have sheet with a macro that unprotect then protect the sheet everytime it runs. Even if sort, filter, and selecting locked cells is enabled, once
you run the macro it will automatically disable these functions.


See attached sheet Vendor Registration Tracking.simple2.xlsm

Please help!

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    
Sheets("Vendor Tracking").Unprotect Password:="123"
  With Target
      If .Count > 1 Then Exit Sub
      If Not Intersect(Range("E6001:E10000"), Target) Is Nothing Then
          Application.EnableEvents = False
          With .Offset(0, 1)
              .NumberFormat = "dd/mmm/yyyy"
              .Value = Date
          End With
          Application.EnableEvents = True
      End If
  End With
  Sheets("Vendor Tracking").Protect Password:="123"
End Sub

[fredlo2010]

Hi bssol,

See if this helps

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    
Sheets("Vendor Tracking").Unprotect Password:="123"
  With Target
      If .Count > 1 Then Exit Sub
      If Not Intersect(Range("E6001:E10000"), Target) Is Nothing Then
          Application.EnableEvents = False
          With .Offset(0, 1)
              .NumberFormat = "dd/mmm/yyyy"
              .Value = Date
          End With
          Application.EnableEvents = True
      End If
  End With
  
  'set false or true the features you want enable or disabled
  Sheets("Vendor Tracking").Protect Password:="123", _
                                    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
End Sub

Thanks