Using two custom sort lists in macro.

This is somewhat related to a post I had submitted previously but a bit more complex https://www.excelforum.com/showthread.php?p=2139259.

I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, D1, or E1.

If the user "double clicks" on cell D1, I want to sort by column D using following custom list:
AA1, AA2, AA3, A1, A2, A3, ZZ1, ZZ2, ZZ3, Z1, Z2, Z3

If the user "double clicks" on cell E1, I want to sort by column E using following custom list:
AAA+, AAA, AAA-, AA+, AA, AA-, A+, A, A-, ZZZ+, ZZZ, ZZ+, ZZ-, Z+, Z, Z-

The issue I seem to be encountering is that I can’t use more than one custom list (e.g., if I use the column D sort list for column D, column E is also using the same list). I can’t seem to apply individual lists to each column.

Would I need to “hardcode” the lists in my macro? In suggestions are appreciated. Thanks.

Please Login or Register  to view this content.

You can hardcode the list in the macro. For example, I created a custom list in the Sorting screen, then recorded a macro of me sorting column A by that custom sort list. In Excel 2007 this is what it recorded:

Please Login or Register  to view this content.

This code will not work in Excel 2003 or 2000, so I suggest you do what I did and record the steps, then modify your code to include the custom sort. Since I don't have 2003 any longer, I can't test, but it might be as simple as updating

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

As I said, the 2007 code won't work in 2003, so try recording a macro of you manually sorting the data and using the custom sort list, then review the new macro for that argument.

Here's an example of someone else's custom sort list: http://www.mrexcel.com/forum/showthread.php?t=34939

Try this:
1. Add the following to the ThisWorkbook module:

Please Login or Register  to view this content.

2. Add a new module and add these two functions to it:

Please Login or Register  to view this content.

then adjust your double-click code to this:

Please Login or Register  to view this content.

and see if that works for you.