VBA to insert/edit cell comment with new cell value

billywiz

Board Regular
Joined
Dec 1, 2004
Messages
57
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
Hi Bill,

You can use this code in Microsoft Excel Objects > ThisWorkbook

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

Code:
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

Threads
1,136,990
Messages
5,678,989
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top