Stop Macro button moving with cells

trevorc · #3 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I stop check boxes moving rows when text wraps in an adjacent cell?	PatrickYork	Excel	2	04-04-2018 11:23 AM
Cannot get macro button to sort with merged cells on sheet	lonniepoet	Excel Programming	3	02-04-2016 04:33 PM
data in some cells moving	Sherriann	Excel	1	08-07-2013 06:52 AM
How to stop letter body moving up and down	alankempster	Mail Merge	2	09-13-2012 08:09 AM
Moving formula range multiple cells when moving sum over one cell	FraserKitchell	Excel	4	02-26-2010 10:38 AM