Custom List Sort with Run-time Error

Lindsay0385

New Member
Joined
Dec 21, 2016
Messages
30
Hello - I hoping someone can help me with this.

I created a macro using the Macro Recorder to sort a column on my spreadsheet by a custom list.

CUSTOM LIST:
PROJECT CANCELLED​
STAGE 1 - PI
STAGE 2 - RD
STAGE 3 - W
STAGE 4 - LV
STAGE 5 - LD
PROJECT COMPLETE

VBA CODE
Code:
    ActiveWorkbook.Worksheets("Test").AutoFilter.Sort. _
        SortFields.Add2 Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, CustomOrder:= _
        "PROJECT CANCELLED,STAGE 1 - PI,STAGE 2 - RD,STAGE 3 - W,STAGE 4 - LV,STAGE 5 - LD, PROJECT COMPLETE" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Test").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The macro works perfectly on my PC. But when I send the file to my coworker, she gets a "Run-time error '438': Object doesn't support this property or method". On her PC, I tried sorting the column by the custom list manually and I realized that the custom list I made doesn't show up when she opens the workbook.

So I did some googling and added a line to the macro to create the same custom list before my initial code:
Code:
Application.AddCustomList ListArray:=Array("PROJECT CANCELLED", "STAGE 1 - PI", "STAGE 2 - RD", "STAGE 3 - W", "STAGE 4 - LV", "STAGE 5 - LD", "PROJECT COMPLETE")
I was hoping that would fix it, but she's still getting the run-time error, even though it works perfectly for me. I'm so confused.

Any help anyone can provide would be appreciated.

Thanks!
Lindsay
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Are macros/VBA enabled on her computer?
Are you both using the same version of Excel, or is she using an older one?

In the VB Editor, go to the Tools menu, and select References and note all the different libraries you have selected.
Have her do the same and compare the lists. Is she missing any selections?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Try changing the Add2 here
Code:
SortFields.[COLOR=#ff0000]Add2 [/COLOR]Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _
to just Add
 

Lindsay0385

New Member
Joined
Dec 21, 2016
Messages
30
Are macros/VBA enabled on her computer?
Are you both using the same version of Excel, or is she using an older one?

In the VB Editor, go to the Tools menu, and select References and note all the different libraries you have selected.
Have her do the same and compare the lists. Is she missing any selections?
We both are on Microsoft Office 16 and have the same selections checked in the References:
-Visual Basic for Applications
-Microsoft Excel 16.0 Object Library
-OLE Automation
-Microsoft Office 16.0 Object Library
 

Lindsay0385

New Member
Joined
Dec 21, 2016
Messages
30
Try changing the Add2 here
Code:
SortFields.[COLOR=#ff0000]Add2 [/COLOR]Key:=Range("B4:B999"), SortOn:=xlSortOnValues, Order:= _
to just Add
THANK YOU! So simple! I wonder why it worked on my PC with no problems.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
If you're both running the same version of Xl, I would have thought it would work for both of you.
Add2 appeared in Xl 2016, although I've no idea what it does, as opposed to Add.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,908
Messages
5,627,578
Members
416,255
Latest member
amethystia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top