Command buttons not sorting - and sometimes jumping around on sort

netxtown

New Member
Joined
Oct 6, 2023
Messages
7
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Buttons have properties related to their connection with cells. Right click button, choose Format Control, then Properties.
 
Upvote 0

Forum statistics

Threads
1,223,384
Messages
6,171,775
Members
452,424
Latest member
Sheila003

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top