VBA to insert/edit cell comment with new cell value

[billywiz]

I use a spreadsheet to manage employee training and refresher training.
Each row holds an employee record. One column is used to enter the date each employee was last trained. In order that I can retain each employee's history of training, I add a cell comment to the date cell in which all of the dates of previous training are kept. Each time the person is retrained, I update the cell with the new date and add the date to the comment as well.

I would like to automate this with a macro which will add the new cell value to the existing comment text, or if this is a new record and the cell has no comment, insert a new comment box and input the cell value.

Thanks.

 

[wongm003]

Hi Bill,

You can use this code in Microsoft Excel Objects > ThisWorkbook

*** NOTE - change intDateCol to column number that contains your dates ***

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo hndl_err

    'CHANGE TO COLUMN WITH YOUR DATES
    Const intDateCol As Integer = 2
    Dim strVal As String
    Dim c As Variant

    For Each c In Range(Target.Address)
        If c.Column = intDateCol Then
            strVal = c.Value
            If IsDate(strVal) Then
                'try to add comment if comment exists trigger 1004 error
                c.AddComment
                c.Comment.Visible = False
                strTemp = strVal
update_comment:
                c.Comment.Text Text:=strTemp
            End If
        End If
    Next c

    Exit Sub
    
hndl_err:
    If Err.Number = 1004 And IsDate(strVal) Then
        'check to see if date already in comments...
        If Not c.Comment.Text Like ("*" & strVal & "*") Then
            strTemp = c.Comment.Text & Chr(10) & strVal
        Else
            strTemp = c.Comment.Text
        End If
        Resume update_comment
    ElseIf Err.number <> 1004 then
        MsgBox Err.Description
    End If

End Sub

 

[ravishankar]

instead of comments, you can store it in another cell and accumulate it with this macro

cells(2,1) = cells(1,1) & ", " cells(2,1) where your date is in A1. each time you change the training date run the macro once.
ravi

 

[billywiz]

Thanks wongm003, that's a superb piece of code, works perfectly.