Calc - Macro to change date and list order

[HenkC.Meerhof]

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 |

[jimk]

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

=HYPERLINK("vnd.sun.star.script:Standard.Module1.click_done?language=Basic&location=application&ROW="&ROW(),"[Done]")

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 com.sun.star.util.SortField
    aSortFields(0).Field = 0
    aSortFields(0).SortAscending = FALSE
    Dim aSortDesc(0) As New com.sun.star.beans.PropertyValue
    aSortDesc(0).Name = "SortFields"
    aSortDesc(0).Value = aSortFields()
	oRange = oSheet.getCellRangeByName("B1:F100")
    oRange.Sort(aSortDesc())
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.

Ctrl-click on A5 to produce this result.

References:

• See https://unix.stackexchange.com/questions/278220/libreoffice-calc-formatting-hyperlinks-advice-outdated for how to format hyperlinks.
• Passing a row parameter was adapted from [Solved] Calc basic macro to add a button to a cell (View topic) • Apache OpenOffice Community Forum.
• Sorting code was adapted from Andrew Pitonyak’s Macro document Listing 6.28.

[HenkC.Meerhof]

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

[HenkC.Meerhof]

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?

[jimk]

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

[jimk]

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