Adding a button to each row of table to send a task to the bottom of the list

[wrequaw]

Hello,

I'm fairly new to Excel and VBA, but have previous coding experience in other common languages, so still getting used to syntax, etc.

I'm trying to make a basic to-do list/personal CRM where there is a table of tasks sorted by the last time I followed up on the task (eg. "2 days ago", "15 days ago"). I want to have a button attached to each row which I can click once I've followed up which will set the "Days ago" to 0, increment the "Followup Count" and send the row to the bottom of the list. (The button also should stay locked to the row with sorting)

Is there a way that I can do this?

Thanks

 

[Peter_SSs]

Adding a button to every row would be a hard way to go I believe. For one thing you would have to keep adding buttons whenever a task was added to the table.
Instead, I would suggest using a double-click in the Date column of the table to trigger the sequence of events. Here is one possible way. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking a date in the table.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Note that the code may not send the row to the very bottom of the table if there are already rows with zero Days Ago.
I have assumed that the 'Days Ago' column is populated with a formula based off the Date column.
Also assumed that this is ListObjects(1) in the sheet.

Test with a copy of your workbook.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  With ActiveSheet.ListObjects(1)
    If Not Intersect(Target, .ListColumns("Date").DataBodyRange) Is Nothing Then
      Cancel = True
      Target.Value = Date
      With Intersect(Target.EntireRow, .ListColumns("Follow Ups").DataBodyRange)
        .Value = .Value + 1
      End With
      .Range.Sort Key1:=.ListColumns("Date"), Order1:=xlAscending, Header:=xlYes
    End If
  End With
End Sub

wrequaw.xlsm
	B	C	D	E	F
1
2	Date	Days Ago	Description	Follow Ups	Followed Up?
3	24/11/2021	24		1
4	18/12/2021	0		1
5	18/12/2021	0		8
6
7
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=TODAY()-[@Date]