Calc - Macro to change date and list order

Hi all, new to macro’s and Basic is from 30 years ago and very rusty.

Goal: to make a maintenance list that show tasks in date order and re-build on completion of a task.
The list has to show visually dates that are overdue=red (background) and on top of the list.
Each line in calc represents a task, columns show details about the task like task_name, what_to_do, what_with, next_date_interval, etc.

Thought: To make a button activated macro that:

  1. on completion of a task change the
    date to the next date by adding the
    amount of day’s stated in the 'next
  2. change the date color to neutral=no color.
  3. re-order the list in the new sort order.
  4. show the new list.

The button is to be set in fx. the first cell of a task line and has to effect only that line.

[Done] | Date | Task | By who | What with | Next date interval |

[Done] | Date | Task | By who | What with | Next date interval |

[Done] | Date | Task | By who | What with | Next date interval |

If [Done] is column A, then enter the following formula in cell A2, and drag down to fill the rest of column A.


Define the macro like this.

Sub click_done(sURL)
	iRow = CInt(Split(sURL, "ROW=")(1)) - 1
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oDateCell = oSheet.getCellByPosition(1, iRow)
	oIntervalCell = oSheet.getCellByPosition(5, iRow)
	oDateCell.setValue(oDateCell.getValue() + oIntervalCell.getValue())
	REM # sort
    Dim aSortFields(0) As New
    aSortFields(0).Field = 0
    aSortFields(0).SortAscending = FALSE
    Dim aSortDesc(0) As New
    aSortDesc(0).Name = "SortFields"
    aSortDesc(0).Value = aSortFields()
	oRange = oSheet.getCellRangeByName("B1:F100")
End Sub

Date color can be handled by going to Format → Conditional Formatting. Range is B2:B1048576. Add Condition 1 Cell value is equal to "" Apply Style Default. Add Condition 2 Cell value is less than TODAY() Apply Style Bad. Press OK.

Now, set up an example like the following.

starting data

Ctrl-click on A5 to produce this result.

result data


Thanks Jim K, for your solution. I’ll try it ass soon as I have a little time to play with LO.

Hi Jim K. On laptop it works like a dream, but the macro has to be stored with the file if possible.
Is using the file on an android/touch screen device possible, as there is no [Ctrl]+mouse click there?

If the macro is stored in the document, then edit the hyperlink and change &location=document.

I am currently downloading an android emulator in order to answer about touch screens. However, the original question was answered fully, so please click :heavy_check_mark: to mark as accepted.