Custom Sort based on a vba function - Pro's only!

[Rx_]

Excel 2007
Everyone has Customer Names where the naming convention follows a internal set of business rules (by everyone, I mean ME! LOL)

The A-Z sort order does not follow the business rules. No problem, I created a cool VBA Function. In Access forms, Access List boxes, or Access Reports, it just works like a charm.

Now, my Access report uses CopyFromRecordset to take a recordset and create a Excel Report that is "saved as" and taken to the field. OK, I can run this in Access customm sort and paste it in properly into Excel the First Time. (Disconnected Dataset)

The Excel report has Auto Filters on every header (naturally).
So, the user can autofilter on A-Z for the Orders column for example.
Now, the user turn off all filters and wants the Customer Name re-sorted according to the custom Function (the sort by A-Z is not what they want).

Here are some choices that might be possible:
1. Create a custom series (like Satu, Sund, Mond, Tues, Wend, Thurs, Frid) but for 4,000 distinct customer names (LOL, it just doesn't sound right?)
2. Create a Macro Enabled workbook, on the Header name (A5) click event, run a procedure that does all kinds of things to make it right.
3. Program a Insert Function for the workbook - and somehow use this to program selecting all the data and sorting the data using Advanced Filter.
4. Any other ideas?

 

[Rx_]

http://www.timeatlas.com/5_Minute_Tips/General/Displaying_Excel_Filter_Selections

This is an interesting article on how to build custom AutoFilters using VBA custom functions.

Now, my problem is creating a Custom Sort based on the AutoFilter.
Instead of sorting from A-Z - sort based on a custom function.