VBA for Custom Sort List Crashing Excel Table

[cmkarnes]

Hello, I have attached a "mock" Excel table to mimic an issue I'm having with a document from work. I have to have the custom sort list through code and a command button for numerous reasons.

When I press the sort button on this attached table, it works; however, when I close the workbook, and it asks me if I wish to save changes, I click "save" then the sheet crashes and the sort changes don't take.

I have not clicked on the command button in the attached document - it was the only way I could save this document for upload on this site.

Can someone tell me what I've done wrong within the coding? At work we use Excel 2010 and from this computer I'm using right now is Excel 2016. Thank you very much.

 

[vletm]

@cmkarnes
Could You test this ... ?

Sub Macro2()
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("Table1[Program Office]"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Legal Office, Budget Office, Maintenance Office, HR Office", DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[cmkarnes]

Hi, It is not clear to me if I'm totally supposed to delete all the previously written code and insert your suggestion, or keep some of the original code I had and then insert yours? I realize a stupid question, but my vba skills are not expert. Thanks!

 

[Kmahraz]

Hello Cmkarnes,
The Code @vletm provided will sort the data as you requested,
you can delete the previous code and substitute with what he provided or you can create a second command button and paste the code he provided

Sub Button4_Click() ' NewCommand button name 
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("Table1[Program Office]"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Legal Office, Budget Office, Maintenance Office, HR Office", DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[cmkarnes]

I see what I was doing wrong - I've got the new coding pasted in and it works absolutely perfect !! Is there anything special I would need to be aware of if I needed to add additional offices within the code? I played with it and added another office name, and it seems to still work fine, but I just want to be sure there's nothing additional I'd need to be cognizant of. Thanks!

 

[Kmahraz]

Just pay attention to this line and change/ Add to your code as you see fit

.SortFields.Add Key:=Range("Table1[Program Office]"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Legal Office, Budget Office, Maintenance Office, HR Office", DataOption:=xlSortNormal

Thank you!
If you like my answer please click on bottom right Like !

 

[cmkarnes]

Hello, sorry to bother you again. I am at work today, and took the code provided, and applied it to the actual project at work. It works great except in the "official" table, the entries for the custom sort list are actually much longer. There is one item in the list that for some reason won't sort properly even though I have entered the list in the proper order within the vba. I am thinking I would need something along the lines of the TRIM function? If this is the case, it was not clear on how to integrate that into the code that was provided to me. Thank you again!

 

[vletm]

@cmkarnes
Are all items in 'offical' table?
How many rows?
You could check even manually that there are no extra characters - before/after and there is 'space' in the centre of 'sortfield'.
Do it have 'header row'? If not then .Header = xlGuess
Could You test that data with Your sample file?
... or send it for me

 

[cmkarnes]

Well, there are 8 Office names in the list within the vba. When you go to the column where the users have entered the office names (via a Data Validation dropdown list), yes, there is a column heading. I am wondering if there is a difference if the users type the office names in manually or choose from a data validation dropdown list?
I have to leave, but will look at this again when I get home and get on the work laptop - this is a huge table (thus far around 250 rows & will keep growing), with around 30 columns, so would have to figure out how to sanitize it for upload. Thanks!

 

[vletm]

@cmkarnes
Shortly ... if data validation let user to write 'own version' then there could be one nasty possible. Check You data validation settings!
Is it always same item (row) which don't sort properly? First/last/any?
Anyway, check that row!

 

[cmkarnes]

Hello, the users cannot override the dropdown list choices for the office names. I went to the source data for the dropdown list of office names, and using the LEN and TRIM functions, realized that the one office name which wouldn't sort correctly had an additional space in it. As a workaround, I inserted a hidden column to the right of the column where users choose the office names, and used the TRIM function for the whole column. I then modified the vba from above to reflect sorting from the hidden column, which made the original column sort correctly.

Now that I have figured out the issue, I was not sure if there was vba that could do the TRIM instead of having to have the hidden column? Thank you for all your help!

 

[vletm]

@cmkarnes
That extra space cannot come there itself. If data validation has done 'right' that cannot happen, but sometimes somebody can do that kind too.
I'm sure that You don't need extra hidden field. If You think that could happen again You can add 'trimming' before sorting ... but there could be something new features someday. You could make one kind of checking macro, which checks that all office names are correct; no extra spaces before, middle, after or something else - just right names!