I have a spreadsheet (no headers) with project names listed in col. A and a
classification in col. B, as follows:

Project1 WUC
Project2 A
Project3 B
Project4 WUC
Project5 A
Project6 A
Project7 WUC
Project8 B

Projects are added frequently to this list by users, and my goal is a VBA
solution to sort the list in a custom order: WUC, then A, then B. To get
myself started, I recorded a macro where I created a custom list, then sorted
by that list. I noticed the recorded code makes a reference to
"OrderCustom:=7", which I realize is the desired position in MY custom list.
However, I know that not every user has the same custom lists I have, so I
how do I adapt my code to help other users sort by the same criteria?

Here's part of my recorded code:

Application.AddCustomList ListArray:=Array("WUC", "A", "B")

'Code to select range of projects

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=7, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I realize the answer may have nothing to do with creating a custom list in
the first place. Thanks for any help to get me on the right track!
--
Steve C

I have come up with my own solution:

Sub CreateOrder()
Dim N As Integer

Application.AddCustomList ListArray:=Array("WUC", "A", "B")

N = Application.GetCustomListNum(Array("WUC", "A", "B")) + 1
'1 added to give true position of desired order in custom list

'Code to select desired sort range

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=N, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

--
Steve C


"Steve C" wrote:

> I have a spreadsheet (no headers) with project names listed in col. A and a
> classification in col. B, as follows:
>
> Project1 WUC
> Project2 A
> Project3 B
> Project4 WUC
> Project5 A
> Project6 A
> Project7 WUC
> Project8 B
>
> Projects are added frequently to this list by users, and my goal is a VBA
> solution to sort the list in a custom order: WUC, then A, then B. To get
> myself started, I recorded a macro where I created a custom list, then sorted
> by that list. I noticed the recorded code makes a reference to
> "OrderCustom:=7", which I realize is the desired position in MY custom list.
> However, I know that not every user has the same custom lists I have, so I
> how do I adapt my code to help other users sort by the same criteria?
>
> Here's part of my recorded code:
>
> Application.AddCustomList ListArray:=Array("WUC", "A", "B")
>
> 'Code to select range of projects
>
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=7, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> I realize the answer may have nothing to do with creating a custom list in
> the first place. Thanks for any help to get me on the right track!
> --
> Steve C