CODEWITHSUNDEEP
vbaexcel
RealLifePM
RealLifePM

Reputation: 60

How to trigger VBA code after adding a comment?

Say if you add a comment to a cell which includes the word "today", then we would like a VBA code to be tiggered to replace the "today" with today's date in that comment. But the problem here is that I could not find an event (or any other way) to know when a comment has been added, or to which cell (range object). Any ideas?

My current stupid solution is to add VBA code inside Worksheet_SelectionChange event (it's a shame that I could not get the old location before the selection change), and then do a For Each loop, check each comment in the sheet, then execute that replacement.

Upvotes: 2

Views: 1889

Answers (2)

Variatus
Variatus

Reputation: 14373

The address of the previously last clicked cell can conveniently be stored in a variable. The code below should be installed in the code sheet of the tab on which the action is expected. It will take note of the ActiveCell when the worksheet is activated and tracks every click thereafter. If there was a comment in the cell last clicked it will replace the word "today" with the current computer date.

Dim PrevCell As Range

Private Sub Worksheet_Activate()
    Set PrevCell = ActiveCell               ' last previously selected cell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Cmt As Comment

    On Error GoTo NoPrevCell
    ' an error would occur if your code crashes for some
    ' unrelated reason and the sheet isn't activated
    ' before resuming work on the same sheet.
    Set Cmt = PrevCell.Comment
    If Not Cmt Is Nothing Then
        With Cmt
            .Text Replace(.Text, "today", Format(Date, "dd-mm-yy"), _
                           1, -1, vbTextCompare)
        End With
    End If

    Set PrevCell = ActiveCell
    Exit Sub

NoPrevCell:
    Worksheet_Activate
    Resume 0
End Sub

I think it's important for this application to have the Replace function recognize both lower and upper case "Today". This is achieved by specifying case-insensitivity in the function itself. I also prefer to have the date format set right here in the function rather than relying on a Short Date format determined in the setup for Windows.

Upvotes: 1

user3598756
user3598756

Reputation: 29421

a workaround could be using a "helper" cell to store the address of the last selected cell so that once the user is done with the comment and selects another cell the event handler would check the "last" cell only

something like what follows (I used cell "A1" as "helper")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cmt As Comment

With Target
    If .Address <> Cells(1, 1) Then
    Set cmt = Range(Cells(1, 1)).Comment
        If Not cmt Is Nothing Then
            With cmt
                .Text (Replace(.Text, "today", Date))
            End With
        End If
    End If
End With

Cells(1, 1) = Target.Address

End Sub

Upvotes: 2

Related Questions