Auto sort with custom list

[mariposa]

I can get my table to auto-sort perfectly upon changes in ascending or descending order using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Range("E8").Sort Key1:=Range("E9"), _
          Order1:=xlAscending, Header:=xlYes, _
          MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

However, I need it in a custom order, so I have tried the following (along with many others ways, but these were the shortest) but cannot get it to work.
Help please and thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Range("E8").Sort Key1:=Range("E9"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="DM,CM,Admin/Clerk,Maint", _
        DataOption:=xlSortNormal, Orientation:=xlTopToBottom
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Range("E8").Sort Key1:=Range("E9"), _
        Order:=xlAscending, CustomOrder:="DM,CM,Admin/Clerk,Maint", _
        OrderCustom:=1, DataOption:=xlSortNormal, Orientation:=xlTopToBottom
    End If
End Sub

 

[Joe4]

Welcome to the Board!

Are you able to get it to sort properly if you do it manually?
If so, then if you turn on the Macro Recorder and record yourself doing that, it should give you the block of code you need to insert into your current procedure.

 

[mariposa]

OMG! Awesome it worked. Originally I tried it yesterday and it did not work. I must not have altered the code and missed something the first time.
So now my full code looks like the following (this way if someone needs help on the same):

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    ActiveSheet.Unprotect "password"
    ActiveWorkbook.Worksheets("sheet name").ListObjects("table name").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets(("sheet name").ListObjects("table name").ort. _
        SortFields.Add Key:=Range("table name[header name]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "DM,CM,Admin/Clerk,Maint" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet name").ListObjects("table name").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
        ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowInsertingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True _
        , AllowFiltering:=True
End Sub

As you can see this also has a password protect feature along with the change trigger.

I must say I love this site!
Thank you, Joe for your help and pointing back on track.