MACRO Buttons moving when sorting

Northern NY Design

New Member
Joined
Sep 24, 2024
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I am unable to reproduce your problem here. Is there anything else in your workbook aside from the buttons and sortable data in Col A ?
 
Upvote 0
I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?
I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?
I've managed to reproduce the problem and the buttons stay with their respective rows. What do the macros do because there may be a better way of achieving the objective?
 
Upvote 0
Yeah, I was thinking myself having a macro button on every row may be overkill.
If it is something like copying the row over to another place, it might be better just to have one button, and then have it either run against the "active" row when the button is clicked, or prompt the user to enter the row number.
 
Upvote 0
Yeah, I was thinking myself having a macro button on every row may be overkill.
If it is something like copying the row over to another place, it might be better just to have one button, and then have it either run against the "active" row when the button is clicked, or prompt the user to enter the row number.
For that one task I would freeze row 1 and have a button there that copies the row of the active cell.

Maybe scroll to that row so that it appears below the button and than confirm with the user that that
is the correct row.
 
Upvote 0
Yep. I was thinking something along those same lines too.
 
Upvote 0
LOL, lots of solutions. So, each row has a COPY and a DELETE button. This is so my managers can copy the specified data (row) from ACTIVE to INACTIVE rosters. The click the @ Button (COPY) and go to the INACTIVE tab and click the PASTE Button for that row, then go back to the ACTIVE tab and click the X Button (DELETE). After that, they have another button that allows them to reorder the roster in alpabetic order.

This is where the problem comes in; when they resort the roster, the MACRO button moves with the row... so for instance, what was ROW 1, after sorting is ROW 3 but the BUTTON is still linked to ROW 1.
 
Upvote 0
Having buttons for every row can be very cumbersome, as if you add more lines you will need to add more buttons.
Have you give thought to the suggestions we gave you? Only have one button, up in row one, which you can use Freeze Panes to always make sure it is visible.
Then you can either have your code run against the active/selected row at the time the button is pushed, or prompt the user to enter the row to run against.

That seems to be a much more efficient and easy to manage solution that having buttons on every row (which I would never do in a million years - you are just making more work and problems for yourself!).
 
Upvote 0
LOL, lots of solutions. So, each row has a COPY and a DELETE button. This is so my managers can copy the specified data (row) from ACTIVE to INACTIVE rosters. The click the @ Button (COPY) and go to the INACTIVE tab and click the PASTE Button for that row, then go back to the ACTIVE tab and click the X Button (DELETE). After that, they have another button that allows them to reorder the roster in alpabetic order.

This is where the problem comes in; when they resort the roster, the MACRO button moves with the row... so for instance, what was ROW 1, after sorting is ROW 3 but the BUTTON is still linked to ROW 1.
We are assuming that row 1 contains the column headings which is normally the case.

Is having column headings out of the question?

The code behind the button could copy and paste and delete the row and also sort the roster in one go.

Alternatively, have a column in the ACTIVE sheet called Status with a Validation List of either blank or Inactive.

When the Status changes to 'Inactive' then the row is copied / deleted and then the INACTIVE worksheet is reordered.

Another option would be to only copy the 'Inactive' rows to the INACTIVE worksheet when that worksheet is activated.
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,822
Members
452,426
Latest member
cmachael

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