Custom Sorting Works Locally but Fails on Shared Document for Other Users

Question

I've encountered an issue where a custom sorting macro in Excel, which uses VBA, works perfectly on my computer but does not sort correctly when executed on a coworker's machine. We are working in the same file but on two different computers. The macro is supposed to sort a range of cells based on a custom order defined in another worksheet within the same workbook.

On my computer, the data sorts according to the custom list as expected. However, when a coworker triggers the same macro on their computer, the data sorts alphabetically instead of following the custom list.

Few high level points below:

• The workbook is shared and used on different computers with potentially different Excel versions.
• The macro security settings are configured to allow macros to run on all machines.
• The custom list is stored within the workbook, and the macro reads this list to perform the sorting.
• The issue persists even though we're working within the same document and not relying on Excel's built-in custom lists feature.

Below is the code that sorts:

    Set ws = ThisWorkbook.Sheets("Submitted to Business")
    Set rulesWs = ThisWorkbook.Sheets("Rules")

    ' Searching for the last row in...
    ruleLastRow = rulesWs.Cells(rulesWs.Rows.Count, "A").End(xlUp).Row ' Rules
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' Submitted to Business

    ' Set the range in Rules sheet
    Set rng = rulesWs.Range("A2:A" & ruleLastRow)

  ' Set the range in Submitted to Business sheet
    Set r = ws.Range("A2", ws.Cells(lastRow, Table.ListColumns.Count))

    ' Apply the sort
    r.Sort Key1:=ws.Range("B1"), Order1:=xlAscending, OrderCustom:=Application.CustomListCount + 1, Header:=xlYes

Answer 1

A custom list, as you surely understood from the code you wrote yourself, is a property of the application, not the workbook. It does not get shipped across computers/Windows accounts along with a .xls* file and once created on a computer, it can persist even after Excel is closed.
Last, even if the custom order was created manually on both computers, you have no guarantee it will be at the same index.

With that knowledge, it becomes easy to determine the parameter to pass into OrderCustom should be a call to a function. The function looks for the specific index of the custom order you created for yourself, and creates it on the target computer if missing.

I let you rename the below function and define the order. Note that the function adds the required 1 (the same you added in OrderCustom:=Application.CustomListCount + 1) itself:

Function MyCustomOrder_RenameThisFunction() As Integer
    Dim result As Integer, customOrder As Variant
    customOrder = Array("B", "C", "A")

    result = Application.GetCustomListNum(customOrder)
    If result <= 0 Then
        Application.AddCustomList ListArray:=customOrder
        result = Application.GetCustomListNum(customOrder)
    End If
    MyCustomOrder_RenameThisFunction = result + 1
End Function