MACRO Buttons moving when sorting

N

Northern NY Design

New Member
Joined
Sep 24, 2024
Messages
25
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?
 
Sort by date Sort by votes
Try this, which will work on whatever row you click on (one code to rule them all!):
VBA Code: 
Sub CLEARROW1()

'   Get row of activecell
    Dim r As Long
    r = ActiveCell.Row
    
    If MsgBox("This is your friendly Lon-bot, letting you know that if you click YES, this driver will be PERMANENTLY removed from the CURRENT DRIVER DATA tab. ARE YOU SURE YOU WANT TO DO THIS, THIS CANNOT BE UNDONE!", vbYesNo) = vbYes Then

' CLEARROW1 Macro
' Use this MACRO to remove a driver from ROW 1 of the CURRENT DRIVER DATA without removing formulas or harming this spreadsheet set.
'
    Sheets("CURRENT DRIVER DATA").Select
    ActiveSheet.Unprotect
    Range("A" & r & ":C" & r).ClearContents
    Range("E" & r).ClearContents
    Range("G" & r & ":AC" & r).ClearContents
    Range("AE" & r & ":AF" & r).ClearContents
    Range("AG" & r & ":AN" & r).ClearContents
    Range("AS" & r & ":AZ" & r).ClearContents
    Range("BE" & r & ":BL" & r).ClearContents
    Range("BQ" & r & ":BX" & r).ClearContents
    Range("CC" & r & ":CJ" & r).ClearContents
    Range("CO" & r & ":CV" & r).ClearContents
    Range("DA" & r & ":DH" & r).ClearContents
    Range("DM" & r & ":DT" & r).ClearContents
    Range("DY" & r & ":EF" & r).ClearContents
    Range("EK" & r & ":ER" & r).ClearContents
    Range("EW" & r & ":FD" & r).ClearContents
    Range("FI" & r & ":FP" & r).ClearContents
    Range("C3").Select
    Sheets("CURRENT DRIVER DATA").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
    
End Sub
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Joe4 said:
Try this, which will work on whatever row you click on (one code to rule them all!):
VBA Code: 
Sub CLEARROW1()

'   Get row of activecell
    Dim r As Long
    r = ActiveCell.Row
   
    If MsgBox("This is your friendly Lon-bot, letting you know that if you click YES, this driver will be PERMANENTLY removed from the CURRENT DRIVER DATA tab. ARE YOU SURE YOU WANT TO DO THIS, THIS CANNOT BE UNDONE!", vbYesNo) = vbYes Then

' CLEARROW1 Macro
' Use this MACRO to remove a driver from ROW 1 of the CURRENT DRIVER DATA without removing formulas or harming this spreadsheet set.
'
    Sheets("CURRENT DRIVER DATA").Select
    ActiveSheet.Unprotect
    Range("A" & r & ":C" & r).ClearContents
    Range("E" & r).ClearContents
    Range("G" & r & ":AC" & r).ClearContents
    Range("AE" & r & ":AF" & r).ClearContents
    Range("AG" & r & ":AN" & r).ClearContents
    Range("AS" & r & ":AZ" & r).ClearContents
    Range("BE" & r & ":BL" & r).ClearContents
    Range("BQ" & r & ":BX" & r).ClearContents
    Range("CC" & r & ":CJ" & r).ClearContents
    Range("CO" & r & ":CV" & r).ClearContents
    Range("DA" & r & ":DH" & r).ClearContents
    Range("DM" & r & ":DT" & r).ClearContents
    Range("DY" & r & ":EF" & r).ClearContents
    Range("EK" & r & ":ER" & r).ClearContents
    Range("EW" & r & ":FD" & r).ClearContents
    Range("FI" & r & ":FP" & r).ClearContents
    Range("C3").Select
    Sheets("CURRENT DRIVER DATA").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
   
End Sub
Click to expand...
Just to clarify; if I use your solution, I can delete all the other CLEARROW macros and just use this one on each row?
 
Upvote 0
Yes, you can have each button point to that one macro.

As we said, alternatively you could also have just one button to push at the top of the sheet (that is always visible, due to freezing the first few rows), and have them prompt for the row number.
 
Upvote 0
One alternative you could use is to forget the buttons all together and format the cells where you had the buttons to look roughly like a button with coloured wide border and a fill plus the appropriate text, then you can trigger Joe4's macro from the worksheet double click event which can automatically work out the row. This would not be affected by sort
 
Upvote 0
offthelip said:
One alternative you could use is to forget the buttons all together and format the cells where you had the buttons to look roughly like a button with coloured wide border and a fill plus the appropriate text, then you can trigger Joe4's macro from the worksheet double click event which can automatically work out the row. This would not be affected by sort
Click to expand...
That is a pretty clever twist to avoid have to create additional controls, and can easily add new rows with copy/paste.
Nicely done!
 
Upvote 0
Northern NY Design said:
Just to clarify; if I use your solution, I can delete all the other CLEARROW macros and just use this one on each row?
Click to expand...
This will do the job.

Please check the ranges just in case I have not entered them correctly.

Is it just the cells without formulas that you are clearing?

What are you going to do with this row?

VBA Code: 
Sub CLEARROW1()

    ActiveWorkbook.Save
   
    ActiveSheet.Unprotect
 
    If MsgBox("This is your friendly Lon-bot, letting you know that if you click YES," & _
      "this driver will be PERMANENTLY removed from the CURRENT DRIVER DATA tab." & _
      "ARE YOU SURE YOU WANT TO DO THIS, THIS CANNOT BE UNDONE!", vbYesNo) = vbYes Then

' CLEARROW1 Macro
' Use this MACRO to remove a driver from ROW 1 of the CURRENT DRIVER DATA
' without removing formulas or harming this spreadsheet set.

      With Sheets("CURRENT DRIVER DATA")
     
        .Activate
     
        .Unprotect
     
        .Range("A1:C1,E1,G1:AC1,AE1:AF1,AG1:AN1,AS1:AZ1,BE1:BL1,BQ1:BX1,CC1:CJ1," & _
          "CO1:CV1,DA1:DH1,DM1:DT1,DY1:EF1,EK1:ER1,EW1:FD1,FI1:FP1").Offset(ActiveCell.Row - 1, 0).ClearContents
     
        .Range("C3").Select
     
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     
      End With
   
    End If
   
End Sub
 
Upvote 0
HighAndWilder said:
This will do the job.

Please check the ranges just in case I have not entered them correctly.

Is it just the cells without formulas that you are clearing?

What are you going to do with this row?

VBA Code: 
Sub CLEARROW1()

    ActiveWorkbook.Save
  
    ActiveSheet.Unprotect
 
    If MsgBox("This is your friendly Lon-bot, letting you know that if you click YES," & _
      "this driver will be PERMANENTLY removed from the CURRENT DRIVER DATA tab." & _
      "ARE YOU SURE YOU WANT TO DO THIS, THIS CANNOT BE UNDONE!", vbYesNo) = vbYes Then

' CLEARROW1 Macro
' Use this MACRO to remove a driver from ROW 1 of the CURRENT DRIVER DATA
' without removing formulas or harming this spreadsheet set.

      With Sheets("CURRENT DRIVER DATA")
    
        .Activate
    
        .Unprotect
    
        .Range("A1:C1,E1,G1:AC1,AE1:AF1,AG1:AN1,AS1:AZ1,BE1:BL1,BQ1:BX1,CC1:CJ1," & _
          "CO1:CV1,DA1:DH1,DM1:DT1,DY1:EF1,EK1:ER1,EW1:FD1,FI1:FP1").Offset(ActiveCell.Row - 1, 0).ClearContents
    
        .Range("C3").Select
    
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
      End With
  
    End If
  
End Sub
Click to expand...
When clicking the X BUTTON in each row I have it deleting the information in the columns in that row that are not formulas; correct. Problem is, right now, when I sort the drivers aphabetically the X BUTTON is moving with the sort which makes it so that when you click on the X BUTTON, it is deleting the wrong row.
 
Upvote 0
Northern NY Design said:
When clicking the X BUTTON in each row I have it deleting the information in the columns in that row that are not formulas; correct. Problem is, right now, when I sort the drivers aphabetically the X BUTTON is moving with the sort which makes it so that when you click on the X BUTTON, it is deleting the wrong row.
Click to expand...
Works perfectly! Thank you.

Similar question, to do the same thing to COPY, I tried the following but am getting a runtime error.
VBA Code: 
Sub COPYDRIVER()

ActiveWorkbook.Save

ActiveSheet.Unprotect

If MsgBox("This is your friendly Lon-bot, letting you know that if you click COPY DRIVER, the HIGHLIGHTED will be COPIED so that you can PASTE it on the HIGHLIGHTED row on the INACTIVE DRIVER DATA tab. ***YOU MUST DELETE THE DRIVER AFTER PASTING THEM TO THE INACTIVE DRIVER DATA TAB***", vbYesNo) = vbYes Then

' COPYDRIVER Macro

' Use this MACRO to COPY a driver's data to be moved from the CURRENT DRIVER DATA tab to the INACTIVE DRIVER DATA tab.

With Sheets(“CURRENT DRIVER DATE”)

.Activate

.Unprotect

.Range("A1:C1,E1,G1:AC1,AE1:AF1,AG1:AN1,AS1:AZ1,BE1:BL1,BQ1:BX1,CC1:CJ1," & _

"CO1:CV1,DA1:DH1,DM1:DT1,DY1:EF1,EK1:ER1,EW1:FD1,FI1:FP1").Offset(ActiveCell.Row - 1, 0).CopyContents

.Range("C3").Select

.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End With

End If

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Command buttons not sorting - and sometimes jumping around on sort
2
Replies
10
Views
183
netxtown
N
G
  • Question Question
Stop sort after data is sorted with button
Replies
6
Views
160
Peter_SSs
Peter_SSs
S
  • Locked
  • Question Question
Linking cells to follow the data when changed
Replies
1
Views
62
Fluff
Fluff
S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
294
SPS41
S
S
  • Question Question
Linking cells
Replies
1
Views
68
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

Share this page

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