Microsoft Office Forums - View Single Post - [Solved] Stop Macro button moving with cells

trevorc · 09-19-2018, 03:08 PM

Thanks for the reply,
What I'm trying to do is have a macro button used in place of having to select the sort button of a table then selecting the sort option, I just need it to be A-Z or Z-A, this part works fine. I will in the future make this a dynamic action on the currently selected column, but only if I can get the button to stay in the active window area and not scroll/move with the cells as I scroll left/right, up/down, by using the scroll bars or by moving cells using the arrow keys.
My code so far...

Code:

Sub Sort_AZ()
        t = ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text
     If ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "A" Then
        ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "Z"
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Add2 Key:=Range("Clipsal_Customers[[#All],[Status]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects( _
          "Clipsal_Customers").Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
        Exit Sub
      Else
        ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "A"
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Add2 Key:=Range("Clipsal_Customers[[#All],[Status]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects( _
          "Clipsal_Customers").Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
      End If
      
End Sub