Sort by Cell Value VBA

[muhleebbin]

Hi Everyone,

I have the following sort code but would like it to be controlled by a selection field in cell E1. Currently this automatically sorts by Department, Last Name and then # of Days (hired). Department will always be the first level of the sort however, I'd like to add a selection field in E1 that would then sort it by that respective selection. E1 would contain # of Days, Office Number and Last Name.

Primary issue would be if Last Name is selected there would be no need for the third key in the current code as well as I'd like it to be Ascending vs the Descending in the current code for the second key. Also if Office Number is selected, I'd like that to be Ascending as well. There are 2 people in certain offices so the third key would still be valid.

Thanks in advance for your assistance!

Sub Sort()

Worksheets("Data").Unprotect
    With ActiveSheet.Sort
        .SetRange Range("A5:AR1001")
        .SortFields.Add Key:=Range("D5"), Order:=xlDescending
        .SortFields.Add Key:=Range("O5"), Order:=xlDescending
        .SortFields.Add Key:=Range("A5"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
    
Sheets("Data").Sort.SortFields.Clear
        
Worksheets("Data").Protect

End Sub

 

[gordsky]

In the absence of a shorter way you could simply add an IF statement to your code that checks what is in cell E! and then sorts based on the cell value
eg IF e1 = *** then sort this way
else if e1 = !!! then sort another way

 

[muhleebbin]

Thank you very much for the suggestion! Sometimes I tend to overthink things but I was able to get it to work.