Add Sorting and Filtering Capabilities to Reports - Part II

In the last post all sortings are done on either an Ascending or Descending order. For example, when we choose "Specialty" in the drop-down list, the entire physician record is sorted based on an ascending order of "Spec1 -> Spec2 -> Spec3 -> Spec4 -> Spec5 -> Spec6". No surprise here. However, how do we customize the above sorting order? What if "Spec3" is the most important specialty for our sales force and we want those physicians to always appear at the top when sorted? Let's further assume that we would like to sort "Specialty" from top to bottom in an order of "Spec3 -> Spec2 -> Spec6 -> Spec4 -> Spec1 -> Spec5". Can we do this?

It turns out we can, by using something called "Custom Lists". To see your current "Custom Lists", assuming that you are using Excel 2007-2010, go to Excel Options -> Popular -> Edit Custom Lists. Our task is to programatically add a new "Custom List" so that Excel VBA may use that list to perform a customized sorting.

Here is the code. Download the example here.

The key section in the above code utilizes a method called Application.GetCustomListNum(), which returns the position of a particular custom list within all the custom lists on end-user's machine. The method will return 0 if the custom list has not been found. Then we can use Application.AddCustomList to add our custom list. Once we know for sure that there is such custom list on the user's machine, we can use .Range().Sort OrderCustom=CustomListNum+1. Note that we always need to make sure to add 1 to the CustonListNum. Don't ask me why.

For those people who do not want to leave their custom lists on end-user's machine when the user closes the workbook, you can add the following code to your excel file's "ThisWorkBook" section.