Command buttons not sorting - and sometimes jumping around on sort

[netxtown]

Using a sort macro on column(s) containing command buttons is resulting in stacked buttons and weird sorts.

25 rows
3 columns
each row is an account
1st column is hard coded worksheet name (not same as account name (i.e. Acct01 is worksheet name; account is "Branscome"))
2nd column is account name provided via hlookup
3rd column is service day (1, 5, 10, etc and includes "no svc" for dead accounts

I have inserted command buttons for each row. The button covers columns 1 & 2 and leaves the 3rd column visible.
The command button is "named", for example, by entering =$D$12 in the formula line when the button is initially selected.
Subsequently, a macro is then assigned to the command button that goes directly to that account's worksheet.

At the top of the account columns (col 1& 2) is a command button to sort by name
At the top of the service day info column is a command button to sort by day.

Without the command buttons on each row all works perfectly. But! When i create and place the command buttons, usually the account sort works ok. But when I do the service day sort, the command buttons are out of order - and with several being placed behind other command buttons.

FWIW - this exact same spreadsheet was created way back when with Excel 2003 - and everything has always worked just fine. That same spreadsheet in Excel 2023 still works just fine. And i am about to go blind trying to figure out what the heck is happening. I have deleted (cut) all of the command buttons on the rows, deleted the sort macros for the two sort buttons at the top of the columns - recreated all of it - and still I got command buttons playing hide n seek and out of order.

I am at a loss, and I need to get this working again. Any ideas?

 

[Logit]

Have you tried 'freezing' the top row and placing all your command buttons there ? Perhaps that would solve your issue.
Also be certain you are using Command Buttons and not the ActiveX buttons.

 

[netxtown]

All buttons are just the command buttons - no activeX
I have replaced all buttons at least once - including the buttons at the top row. Heck I have even deleted the associated macros (sort) trying to find a culprit of some kind!

If I remove all of the "account" command buttons, the sorting by the two buttons at the top of each column work just fine. It is only the stack of 25 command buttons that are doing some strange stuff.

 

[netxtown]

I have been trying different scenarios in an attempt to discover what plagues this sort. I opened an entirely new workbook. Created the same account#/account name/ svc date columns. Created two macros - one to sort account names and the other to sort svc dates. Assigned the macros to separate command (not activeX) buttons placed at the top of each column. next i created command buttons - one for each account - and used the =cell via the formula line so that the button would automatically pick up the account name when the sorting occurs. Every button was set to do not move or size with cells"

I placed the account command buttons a couple columns over from the actual data - and then did the sorting. All worked perfectly. BUT!! If i move those same buttons to ride over the data in the columns - all heck breaks lose and the buttons stack and hide and move to positions completely irrelevant to the data - or to the button 'name'

I moved the buttons back to the previous position, converted any formulas in the data to values and tried again. Same result. Scattered stack of buttons.

I am at a loss - and i don't know how to remove any hidden "link" between the button and the cells. Bear in mind - the sort goes fine - it is what the sort does to the buttons.

 

[netxtown]

I removed all command buttons. Deleted both of the sort macros. Created 2 NEW sort macros for the 3 columns (Acct#, Name, Svc Date). Sort columns are Name and Svc Date.
Next I inserted a rectangle shape, colored it up, and added a 3d faction. Duplicated that shape 25 times - one for each account. Added the "=A1" via the formula line as "A1" corresponds to each cell in the Name column ( A2-A26). That formula for each object picks up the Name of the account and displays it on the object.

Hit the sort button for the Svc date and perfect!! Hit the Name sort button to go back to alphabetical order - and disaster again. And finally - if i take the entire stack of "buttons" and move them a couple-two-three columns over - they will sort just fine for either sort macro.

I don't fricking get it! Is there some kind of imposed or secret or hidden link when a button or object floats over a cell?? I don't understand how a cell sort impacts the objects floating above it

 

[Logit]

Buttons have properties related to their connection with cells. Right click button, choose Format Control, then Properties.

 

[netxtown]

I'm going to close out this thread as it seems Excel 2021 is just not cut out to sort anything other than actual cell data - and keep it straight. And, I am running out of time as this new workbook has to finished and ready to go by Jan 01, 2025.

 

[Peter_SSs]

I'm going to close out this thread ..

That's fine and your comment indicates you no longer are looking for help on this, but the 'Mark as solution' tick at the right is to mark the solution, not to close the thread, so I have removed the tick.
We don't actually close threads in this forum because it may be that some time later a solution might be posted, or a better solution even if one has already been provided.

 

[netxtown]

I'm going to close out this thread as it seems Excel 2021 is just not cut out to sort anything other than actual cell data - and keep it straight. And, I am running out of time as this new workbook has to finished and ready to go by Jan 01, 2025.

First off - I do NOT know why this worked. I will lean towards "sort" issues within Excel - but I have nothing to prove that.

SO.... I did finally get the sort of the macro buttons to work - and each button to remain with its cell on the sort.

I have 3 columns: Acct #, Acct Name, Service Date
I have 25 Accounts (so 25 rows). The number of rows will NOT change.
I have 25 command buttons (one for each account with the button labeled accordingly. Each button has it's own "GoTo_(worksheet)" assigned macro

I took each row and merged the cells for Acct# and Acct Name. By default, Excel only keeps the left most cell info.
Next I created command buttons - each spans almost the entire width of the merged cells, with a height that is .01 less than the height of the cell
Each command button was placed and edited to reflect the account name, and the proper "GoTo" macro assigned. !!! > Make certain the button is within the confines of the merged cells.
Lastly, I selected the rows of merged cells - and then UN-merged them. The left most column shows the account number and the other column is now blank.

I can now sort by Service Date or by Acct# and all works just as I have hoped it would.
WHEW!

 

[Logit]

I believe your solution lies right here :

'Make certain the button is within the confines of the merged cells.'