I'm almost done with a really critical excel sheet that would be shared with different users to update frequently. I'm using some VBA which I believe trigger the problems.

Two issues I'm facing now:

1) Once the sheet is in protection mode, the sort functions within the sheet are automatically disabled even that I do enable them during protecting process. I have not sound a solution for this.

2) when the excel sheet is shared, and the user do anything with Macro functions, this error massage appears and the functions are not working anymore.

run time error 1004 - unprotect method of worksheet class failed

See attached sheet
Vendor RegistrationTracking.xlsm


VBA Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Sheets("Vendor Tracking").Unprotect Password:="Dashboard"
  With Target
      If .Count > 1 Then Exit Sub
      If Not Intersect(Range("E6001:E10000, G6001:G10000, O6001:O10000, Q6001:Q10000, S6001:S10000, U6001:U10000, W6001:W10000, Y6001:Y10000, AA6001:AA10000, AC6001:AC10000, AE6001:AE10000"), 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:="Dashboard"
End Sub
Please help!