title: Using a Command Button for 2 different sort functions
body: <p>This is my first post using Stack Overflow but i have definitely taken advantage of the wonderful database of vba knowledge present. I am going to give a short introduction of the purpose for seeking help, then list the code that goes along with it. </p>
  
  <p>I have been given a list of employees that consists of names, # of years worked, shift, Test site and position in the company and am asked to create a list of employees for weekend overtime. I have been able to generate the list based on user inputted values of saturday and sunday along with number of hours they want to work and etc. Now I am beginning to create a sorting function based on a list of 7 priorirties that will change week to week. </p>
  
  <p>I found a wonderful post that has allowed me to create a command button that will change its comment box based on the user clicking the button. The first click will show "Site Priority On" and the second click with show "Site Priority Off." It will then continue to change back and forth every click. When clicked the first time it runs a macro to sort based on a custom list of priorities that can be inputed by the user, and the second click runs a macro that returns the list back to its original layout. And so on and so forth from 1 sort to the other every click.</p>
  
  <p>Now, everything works fine for the second macro no matter how many times the button gets pressed. However, my customsort list will only work in the descending order the first click. After the button gets back to sorting based on priority it flips my order from descending down the column list to ascending up the list from the least priority to the greatest. I have tried adding code to delete the custom sort order and recreating it everytime it runs the macro but it continues to switch to ascending order after the first run.</p>
  
  <p>Here is my code for the Command BUtton: </p>
  
  <pre><code>Private Sub CommandButton4_Click()
  
  Select Case CommandButton4.Caption
      Case "Site Priority Off"
        Call PrioritySort
        CommandButton4.Caption = "Site Priority On"
      Case "Site Priority On"
        Call SortOff
        CommandButton4.Caption = "Site Priority Off"
    End Select
  End Sub
  </code></pre>
  
  <p>Here is my code for the customsort:</p>
  
  <pre><code>Sub PrioritySort()
  
  
    ActiveWorkbook.Worksheets("Preliminary List").Sort.SortFields.Clear
    Range("S12:Z55").Select
    Application.AddCustomList (Range("T3:T9"))
    Selection.Sort key1:=Range("V12:V55"), OrderCustom:=6, Header:=xlYes, _
    MatchCase:=False, Orientation:=xlTopToBottom
  
  end sub
  </code></pre>
  
  <p>When I run the sort the second time using the command button I have opened the sort option by hand and looked what the level says for the sort order. It gives the options of Smallest to Largest, Largest to Smallest, Custom sort list descending (it is usually something like 3B, 6, 7, 3E), custom sort list ascending (it is always the same list but backwards like 3E, 7, 6, 3B) and then the custom sort (the usual option).</p>
  
  <p>Can anyone think of a reason for the code to change from descending to ascending after being run twice? And why after the second click it will always choose the ascending order no matter how many times it runs.</p>
  
  <p>Thanks</p>