Maybe you can add the customlist, use it and delete it all in code:

Option Explicit
Sub testme01()

Dim myArr As Variant
Dim myListNumber As Long

myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon")
Application.AddCustomList listarray:=myArr
myListNumber = Application.GetCustomListNum(myArr)

Sheets("Fruit").Select
Rows("5:225").Select

Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=myListNumber + 1

Application.DeleteCustomList myListNumber

End Sub

Untested, uncompiled. Watch for typos.


Also wrote:

Slightly odd one.
I want to sort a sheet in order of, for example, "Grape, Apple, Orange,
Banana, Melon".
Of course at the moment it will just sort alphabetically.
I can use the CustomList option BUT this spreadsheet needs to be shared with
others and I'm still on Excel 2000...

The sort it self will be called by a Macro:

Sheets("Fruit").Select
Rows("5:225").Select
Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:XXXX, MatchCase:=False, Orientation:=xlTopToBottom

Where "Fruit" is the sheet, 5:225 are the rows to be sorted and X6 has the
Melon, Grape etc.
Of course using Custom Lists on my machine XXXX = 8. But on my colleagues
they don't have the custom list and I would prefer not to add it if possible
but to keep the sort order in the Macro.
(The list I want to sort by is at:
Sheets("Types").Range("B54:B60")
Any ideas? I tried subbing it in and it didn't like it...

Thanks and Happy New Year in advance.

--

Dave Peterson