Defined Order List

[Laura]

Bob Philips thanks for your help earlier but I have
another problem.
I've created the custom list (to have a defined order for
sorting) using Tools>Options>Custom Lists and then put it
into my macro as code (below). However as the file is
going to be on a network to be accessed by many users,
this custom list won't work as it's specific to my machine
or log on profile/account does anyone know how I can put
the list into the code? Help is much appreciated

Range("A1").Sort _
Key1:=Columns("B"), _
Key2:=Columns("C"), _
Order2:=xlAscending, _
Key3:=Columns("F"), _
Order3:=xlDescending, _
Header:=xlYes, _
OrderCustom:=6, _ 'this is my custom list
MatchCase:=False, _
Orientation:=xlTopToBottom

I thought about using
Application.AddCustomList ListArray:=Array
("General", "Engineering", "Maintenance", "Supply Support")
but other users might have other custom lists and this
might not necessarily be number 6 as used in the code
above?

 

[Chip Pearson]

Laura,

See the GetCustomListNum property.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Laura]

Cheers,
That works but strangely the number it returns
n = Application.GetCustomListNum(Array
("General", "Engineering", "Maintenance", "Supply
Support"))
is one less than it actually is i.e n is returned as 5,
when it is actually 6, I know i can put
OrderCustom:=n + 1
but am worried that when other users run the code on diff
machines 'n' might be return correctly therefore n+1 will
cause an error? I'm using Access 97. Any ideas, please?