Ask Your Question

Calc - Macro to change date and list order

asked 2017-10-22 14:11:50 +0200

Henk C. Meerhof gravatar image

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 |

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-10-23 16:26:12 +0200

Jim K gravatar image

updated 2017-10-23 23:48:57 +0200

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


edit flag offensive delete link more


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

Henk C. Meerhof gravatar imageHenk C. Meerhof ( 2017-10-24 16:41:26 +0200 )edit

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?

Henk C. Meerhof gravatar imageHenk C. Meerhof ( 2017-10-24 19:03:35 +0200 )edit

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

Jim K gravatar imageJim K ( 2017-10-24 20:22:35 +0200 )edit

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.

Jim K gravatar imageJim K ( 2017-10-24 20:32:56 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-22 14:11:50 +0200

Seen: 371 times

Last updated: Oct 23 '17