MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with custom sort macro

Posted by Jeramy on December 24, 2001 4:54 AM


I have data in a program that needs to be sorted after a new entry. The lists cant be sorted alphabetically 'cause they have letters and numbers, so I made a custom list to sort by. Everything works fine now, but here is my delema. When someone else tries to run the program on another computer the custom list (#6 in my case) isnt there. Is there any way to imbed the custom list with the program or some way to tell excel how to sort no matter what computer it is running on.


Posted by Damon Ostrander on December 25, 2001 11:19 PM

Hello Jeramy,

Simply add the following code to your ThisWorkbook event code:

Private Sub Workbook_Open()

Application.AddCustomList ListArray:=Array("King", "Queen", "Bishop", "Knight", _
"Rook", "Pawn")
End Sub

where the list I show is just an example. If your list is in a range of cells, you can also load the list directly from the cells via the syntax:

Application.AddCustomList ListArray:=Worksheets("Sheet1").[a1:a100]

where the range a1:a500 contains your list.

If you are not familiar with installing a workbook event macro, it is quite easy. Just right-click on the Excel icon that is on the left end of the worksheet menu bar (just to the left of the File menu item). In the dropdown menu select View Code... Paste the macro code into the VBE code pane that appears.

Happy computing.