Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be sure of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix) variable,
.... No go.
The only way seems to be to add the custom list, find the number and use it.

A kiss for a smart solution ;-).

Frans

Basically, you have it right, you need to add it, then sort on the new
number

OrderCustom:=Application.CustomListCount

But guess what, there is a problem. See this article

http://support.microsoft.com/default.aspx?kbid=134913
XL: GetCustomListNum Returns Unexpected List Number

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Frans van Zelm" wrote in message
...
Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be sure
of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix)
variable,
... No go.
The only way seems to be to add the custom list, find the number and use
it.

A kiss for a smart solution ;-).

Frans

Hi Frans,

Adding your list is subject to a problem if the lists aleady exists.

According to VBA help:

"If the list you're trying to add already exists, this method does nothing".

However, my experience is that attempting to add a list that aleady exists,
causes a 1004 run-time error.

In either event, attempting to add a list will fail if the list already
exists.

A workaround, that works for me, is to include a spurious entry as the
first item of the list. This way the new list will always be accepted and
the list's postion will always be after any existing lists, i.e the new list
will always be in position: Application.CustomListCount.

Unlike the GetCustomListNum method, I am not aware of any problem with use
of the CustomListCount property.

If the spurious item is chosen such that it will not be found in any data to
be sorted, it should have no adverse impact.


---
Regards,
Norman


"Frans van Zelm" wrote in message
...
Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be sure
of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix)
variable,
... No go.
The only way seems to be to add the custom list, find the number and use
it.

A kiss for a smart solution ;-).

Frans

Hi Franz,

Retesting under xl2000, I was able to reproduce the problem mentioned in
Bob's MSKB link with a sort expression like:

OrderCustom:=Application.CustomListCount

Using the KB suggestion, and replacing the sort expression with:

OrderCustom:=Application.CustomListCount +1

resolved matters however.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Frans,

Adding your list is subject to a problem if the lists aleady exists.

According to VBA help:

"If the list you're trying to add already exists, this method does
nothing".

However, my experience is that attempting to add a list that aleady
exists, causes a 1004 run-time error.

In either event, attempting to add a list will fail if the list already
exists.

A workaround, that works for me, is to include a spurious entry as the
first item of the list. This way the new list will always be accepted and
the list's postion will always be after any existing lists, i.e the new
list will always be in position: Application.CustomListCount.

Unlike the GetCustomListNum method, I am not aware of any problem with use
of the CustomListCount property.

If the spurious item is chosen such that it will not be found in any data
to be sorted, it should have no adverse impact.


---
Regards,
Norman


"Frans van Zelm" wrote in message
...
Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be sure
of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix)
variable,
... No go.
The only way seems to be to add the custom list, find the number and use
it.

A kiss for a smart solution ;-).

Frans

Hi Norman,

the problem with this is that it is not consistent, so do you add or not?

Bob


"Norman Jones" wrote in message
...
Hi Franz,

Retesting under xl2000, I was able to reproduce the problem mentioned in
Bob's MSKB link with a sort expression like:

OrderCustom:=Application.CustomListCount

Using the KB suggestion, and replacing the sort expression with:

OrderCustom:=Application.CustomListCount +1

resolved matters however.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Frans,

Adding your list is subject to a problem if the lists aleady exists.

According to VBA help:

"If the list you're trying to add already exists, this method does
nothing".

However, my experience is that attempting to add a list that aleady
exists, causes a 1004 run-time error.

In either event, attempting to add a list will fail if the list already
exists.

A workaround, that works for me, is to include a spurious entry as the
first item of the list. This way the new list will always be accepted
and
the list's postion will always be after any existing lists, i.e the new
list will always be in position: Application.CustomListCount.

Unlike the GetCustomListNum method, I am not aware of any problem with
use
of the CustomListCount property.

If the spurious item is chosen such that it will not be found in any
data
to be sorted, it should have no adverse impact.


---
Regards,
Norman


"Frans van Zelm" wrote in message
...
Hi programmers,

In Excel 2K
Selection.Sort ... OrderCustom:=xx
seems to work only using the number of a custom list. But how to be
sure
of
this number on any other computer then your own?
I tried to refere a range on a sheet, to create and use a (matrix)
variable,
... No go.
The only way seems to be to add the custom list, find the number and
use
it.

A kiss for a smart solution ;-).

Frans

Hi Bob,

Using Application.CustomListCount +1 in the Sort statement, seemed to work
consistently for me - but I cannot vouch for this as a universal solution.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Hi Norman,

the problem with this is that it is not consistent, so do you add or not?

Bob

I first used CustomListCount in a solution I provided, and I did not have to
add 1. So I was surprised when in another response I gave, the OP responded
with a problem which we traced to this inconsistency. So, I have never seen
it, you seem to always see it, and I hate inconsistency :-).

Regards

Bob


"Norman Jones" wrote in message
...
Hi Bob,

Using Application.CustomListCount +1 in the Sort statement, seemed to work
consistently for me - but I cannot vouch for this as a universal solution.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Hi Norman,

the problem with this is that it is not consistent, so do you add or
not?

Bob