Custom List Being Ignored

[FusionRed]

I created a custom list to sort by (following is the order I need: 35,40,38,34 I invoked it with the options and ok) and when I go to sort by that column, Excel is ignoring my sort order. Anyone have any ideas why Excel is ignoring this and sorting by 34, 35, 38, 40 (a numeric order)?????

Thanks in advance for your help.

FusionRed

[mvidas]

Hello again,

When you go to sort, are you clicking the Options button of the Sort window, and choosing the custom list you created? Note that it will only apply to your first 'sort by' range.

Or are you doing this via VBA?
Matt

[FusionRed]

I've been going to sort and clicking on options. I've defined the sort order based on a function which returns an integer in column "E" and then I sorting by name in column "D". The custom list just seems to be getting ignored completely.

FusionRed

[mvidas]

As an FYI, if you're doing this in VBA, then you would use code like:

Dim SortNum As Integer
 SortNum = Application.GetCustomListNum(Array(35, 40, 38, 34))
 Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
  OrderCustom:=SortNum + 1, MatchCase:=False

But that will only work if you want the custom list sorted as the 'primary sort' (first sort by range). If, as an example, you need your sheet sorted by columns B, then C, then A, but A is the one with the custom sort, then you could use code like:

Dim SortNum As Integer
 SortNum = Application.GetCustomListNum(Array(35, 40, 38, 34))
 Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
  OrderCustom:=SortNum + 1, MatchCase:=False
 Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2"), Order2:= _
  xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False

It sorts the custom list first, then sorts the remainder using normal sorting. Since excel keeps the previous sort, this is the way to get around that.

Matt

[mvidas]

(sorry, didn't refresh before posting that).
Is there a way you can attach a sample spreadsheet?

[FusionRed]

This spreadsheet has merged cells and that's the error I'm getting when I try the code above. The sheet looks like the following

B C D E F G ...

Project# Description Resource ColorIndex Jan Feb .....

xxxxxx

Actual data begins on B11 column head in B10. There are many subtotals which need to be taken out of the sort which is why I wanted to just select the items for each project and sort by columns E then D (colorindex and resource) leaving my subtotal position unchanged.

I am still mystified why Excel would ignore the custom list, in fact, it worked yesterday but won't today.

I'm thinking of reinstalling Excel 2000 at this point in the hope that the program will respond to the custom list properly. However, I am unsure where the macros are stored so that I can save them before the reinstall. Do you know what the mapping to the macros is????

Many thanks,

FusionRed

[Jacob Hilderbrand]

This spreadsheet has merged cells and that's the error I'm getting when I try the code above.

Merged Cells and Excel do not get along well. Instead of Merge use Center Across Selection (Format | Cells | Allignment).

However, I am unsure where the macros are stored so that I can save them before the reinstall.

Macros are stored in each Excel file or Addin. You may have some code in Personal.xls which you might want to save. Open a blank workbook. Press Alt + F11. In the Project Explorer check for Personal.xls or any other files. Double click each item to see if there is any code when the Code Window opens.

[FusionRed]

Hi Jake:-)

Thanks for the help. Unfortunately, these reports are coming out of another system and would require a great deal of manipulation, deleting and reinserting of data, so I've decided to take the sort I've got and just move the data where I need it. Not a great solution, but one that saves time.


I found the main folder holding the macros, the path is:

documents and settings/what ever your logon name is/application data/microsoft/excel/xlstart

This will bring you directly to PERSONAL.xls which holds (in my case all of my macros) and can be easily copied and backed up.