Automatically updating Changed and setting up a new line

In order to make things easier, a couple of things are triggered when you work on a todo list item. The following code snippet achieves two things:

• When you edit an existing todo item, it automatically updates the “lastChanged” column.
• When you are working on a new line to create a new todo item, it sets up the line with the proper formatting and formulas.

Const ncNr = 1
Const ncText = 2
Const ncNew = 4
Const ncWhen = 5
Const ncStatus = 6
Const ncChanged = 7
Const ncStatus = 8
Const ncPrio = 9
Const ncComment = 10

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncChanged) And _
    (Target.Row <> 1) Then
    Cells(Target.Row, ncChanged) = Now
End If

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncNew) And _
    (Target.Row <> 1) And _
    Cells(Target.Row, ncNew) = "" Then
        Cells(Target.Row, ncNew) = Now
        Cells(Target.Row, ncStatus) = ToDo.scStatusOpen
        'copy formulas to id and status columns
        Cells(Target.Row - 1, ncNr).Copy Destination:=Cells(Target.Row, ncNr)
        Cells(Target.Row - 1, ncRelevance).Copy Destination:=Cells(Target.Row, ncRelevance)
End If

End Sub

Here’s a brief explanation of this code:

• Constants (lines 1 to 9) In order to reference the columns in the code, I’ve created constants so that when the layout of the sheet changes (adding/deleting a column), all is needed is a change to the constant.
• Sub Workbook_SheetChange (line 11) In order for the code to be automatically called, you have to implement the sub Workbook_SheetChange. Whenever a cell in the workbook is changed, Excel calls this sub with two parameters denoting the sheet that was changed and the particular cells that were changed. (You can change more than one cell at a time, e.g. when selecting a range of cells and changing the cell’s background color.)
• Updating the Changed-Column (lines 13-17) The if-statement checks that only one cell is selected (line 13), it’s not the changed-column (line 14 – this would trigger an endless loop), and we’re not in the first row (line 15). Typically, this means that the user has changed something in an existing todo item, for example the title, the due-date, the status or the comment. If these conditions apply, then the changed-column is updated to the current date/time (line 16).
• Setting up a new line (lines 19-28) The if-statement performs similar checks as above, but also checks if the new column is still empty (i.e. that we have a new todo item). Then the new line is set up accordingly. The new column is set to Now (line 23) and the status is set to open (line 24). Then we have to set up the formulas in the id and relevance columns. The way this is done is by copying the cells from the line above. If these are set up correctly (e.g. id as a formula adding one to the value above), then the formulas work in the new row as well.

This way, you can just focus on working with the todo items without having to think about how to make the filtering work.

Next:Calculating the relevance of an item.