VBA sort is moving the form control buttons along with the data

[MattCSI]

I have about 200 rows of data, each with multiple form control (not the ActiveX control) buttons. I create the buttons named according to their row, and set the .placement property to "don't move or size with cells"

bBtn.Placement = xlFreeFloating

That part is working, and I can see that the buttons are named and placed properly. The problem arises when I sort the rows. The buttons move, regardless of the .placement setting. I'm using this code to sort:

set wb = ThisWorkbook
set ws = ThisWorksheet
set rFullRange = ws.Range(Cells(lFirstRow, 1), Cells(lLastRow, lLastCol))
rFullRange.Sort key1:=ws.Columns(lSortCol), order1:=xlAscending, key2:=ws.Columns(lJobNumCol), order2:=xlAscending, Header:=xlNo

I didn't include the full code - the variables correspond to column and row numbers. The sorting code is also working fine. My problem is just figuring out how to not have the buttons move when I sort.

Is there any way to accomplish that?

 

[MattCSI]

I've continued to work on this today, taking another approach to sorting, and I found something strange and frustrating.

For background, my range begins on row 14, and that row is a filtered header row. I found this morning that if I use Excel's UI dropdown at the top of a column to sort that column, it sorts the whole range very quickly and the buttons don't move. That's perfect.

Hoping I was on to something, I recorded a macro of that process. I adapted the code, and used it to replace the sorting code in my previous subroutine. But when I ran that subroutine, it sorted slower, visibly selected the entire range (which didn't happen using the UI), and most frustratingly, it moved the buttons.

Assuming I had messed up in adapting the code, I recorded a fresh copy of the same macro.

This time I ran the macro as-is. I did not modify it in any way, and did not add it to my own code. When I ran it, instead behaving as it did while I was recording, it selected the whole range, sorted the data, and moved the buttons.

Why would the recorded code behave so differently than the operation I recorded it from? Does anyone know how to make sort work exactly like it does when using the UI?

Here is the full unedited macro for reference:

Sub autofiltersort3()
'
' autofiltersort3 Macro
'

'
    ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "A14:A175"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Main").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub