Using VBA to Sort a Table with a Custom List

[sxhall]

Hi,

Thanks for looking at this...

I have the code below that takes a custom sorted list from a Listbox in a Userform puts the items in to an Array and then creates a custom sort to be applied to a table called 'RotaOutbound'.

The code all works fine when I tested it on a list in a column but when I added in the details for the table to sort by the custom list it does not like it!

When running it stops and highlights ' OrderCustom' in blue, no error message! I was getting error '448 Named Argument Not Found' at one point but shortened the code to the below and the message stopped showing, just highlighting, as mentioned, in blue.

Appreciate any help and advice given as this has taken me several hours of googling and cobbling of code together to get this far and have hit the wall now ?

Private Sub CommandButton4_Click()

Dim nIndex As Integer
Dim vArray() As Variant
Dim n As Long

ReDim vArray(ListBox1.ListCount - 1)

For nIndex = 0 To ListBox1.ListCount - 1
  vArray(nIndex) = ListBox1.List(nIndex)
Next

Sheet3.Activate

Application.AddCustomList vArray()
n = Application.CustomListCount

With Sheet3
    Range("RotaOutbound").Select
    .ListObjects("RotaOutbound").Sort.SortFields.Add2 Key:=Range("RotaOutbound[Team]"), _
    SortOn:=xlSortOnValues, OrderCustom:=n + 1, DataOption:=xlSortNormal
    .Sort.SortFields.Clear
End With

Application.DeleteCustomList n

Unload Me

End Sub

Regards and Thanks

Steven

 

[Fluff]

How about

With Sheet3.ListObjects("RotaOutbound").Sort
   .SortFields.Add2 Key:=Range("RotaOutbound[Team]"), _
      SortOn:=xlSortOnValues, CustomOrder:=Join(vArray, ","), DataOption:=xlSortNormal
   .Header = xlYes
   .Apply
   .SortFields.Clear
End With

 

[sxhall]

How about

With Sheet3.ListObjects("RotaOutbound").Sort
   .SortFields.Add2 Key:=Range("RotaOutbound[Team]"), _
      SortOn:=xlSortOnValues, CustomOrder:=Join(vArray, ","), DataOption:=xlSortNormal
   .Header = xlYes
   .Apply
   .SortFields.Clear
End With

Worked first time thanks Fluff.

I can now edit my code down to remove the adding and removal of the custom sort list as well. Will do that now too.

Can now impress my colleagues once again with Excel magic

Steven

 

[Fluff]

You're welcome & thanks for the feedback.