Excel 365: event when adding a comment

[ViroMajor]

[FONT=Monaco, Menlo, Consolas, Roboto Mono, Andale Mono, Ubuntu Mono, monospace]Hi,



My goal is to trigger a macro when a comment is added to a cell (otherwise, the cell is not modified) from the column B.

I was given this script to place at the root of the sheet, which applies to a given cell (B2) instead of the column B.

[/COLOR][/FONT][COLOR=#770088][FONT=inherit]Private[/FONT][/COLOR][COLOR=#141414][FONT=inherit] [/FONT][/COLOR][COLOR=#770088][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#141414][FONT=inherit] Worksheet_SelectionChange[/FONT][/COLOR][COLOR=#141414][FONT=inherit]([/FONT][/COLOR][COLOR=#770088][FONT=inherit]ByVal[/FONT][/COLOR][COLOR=#141414][FONT=inherit] Target [/FONT][/COLOR][COLOR=#770088][FONT=inherit]As[/FONT][/COLOR][COLOR=#141414][FONT=inherit] Range[/FONT][/COLOR][COLOR=#141414][FONT=inherit])[/FONT][/COLOR]
<code class=" language-vb" style="box-sizing: border-box; font-family: inherit; font-size: 1em;">[COLOR=#770088]If[/COLOR] Target.Address = [COLOR=#AA1111]"$B$2"[/COLOR] [COLOR=#770088]Then[/COLOR]
[COLOR=#770088]If[/COLOR] HasComment(Target) [COLOR=#770088]Then[/COLOR] [D2] = [COLOR=#AA1111]"test réussi"[/COLOR]
[COLOR=#770088]End[/COLOR] [COLOR=#770088]If[/COLOR]
[COLOR=#770088]End[/COLOR] [COLOR=#770088]Sub[/COLOR]
[COLOR=#770088]Private[/COLOR] [COLOR=#770088]Function[/COLOR] HasComment(Cell [COLOR=#770088]As[/COLOR] Range) [COLOR=#770088]As[/COLOR] [COLOR=#770088]Boolean[/COLOR]
[COLOR=#770088]Dim[/COLOR] oComment [COLOR=#770088]As[/COLOR] Comment
[COLOR=#770088]On[/COLOR] [COLOR=#770088]Error[/COLOR] [COLOR=#770088]Resume[/COLOR] [COLOR=#770088]Next[/COLOR]
[COLOR=#770088]Set[/COLOR] oComment = Cell.Comment
[COLOR=#770088]If[/COLOR] [COLOR=#770088]Not[/COLOR] (oComment [COLOR=#770088]Is[/COLOR] [COLOR=#221199]Nothing[/COLOR]) [COLOR=#770088]Then[/COLOR] HasComment = [COLOR=#221199]True[/COLOR]
</code>[COLOR=#770088][FONT=inherit]End[/FONT][/COLOR][COLOR=#141414][FONT=inherit] [/FONT][/COLOR][COLOR=#770088][FONT=inherit]Function[/FONT][/COLOR][FONT=Monaco, Menlo, Consolas, Roboto Mono, Andale Mono, Ubuntu Mono, monospace][COLOR=#141414]

This piece code is functionnal with Excel 2013 but inactive with the latest Office 365 version.

It seems that the comments’ integration was changed since Excel 2016. Cosmetically, it is now different, but programatically, it seems also...

Any idea how to fix / adapt this code to make it work ? Thanks a lot (my skill level is between noob and intermediary—)


[/FONT]

 

[James006]

Hello,

You could test following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
 If HasComment(Target) Then Target.Offset(0, 2) = "Test Réussi"
End Sub



Private Function HasComment(Cell As Range) As Boolean
Dim oComment As Comment
On Error Resume Next
Set oComment = Cell.Comment
If Not (oComment Is Nothing) Then HasComment = True
EndFunction

Hope this will help

 

[ViroMajor]

Comments’ type from Excel 2013/2016
https://ibb.co/YQbgSNQ

Comments’ type from current Office 365
https://ibb.co/Fb4d6Jr
https://ibb.co/j3ZRwTf

Thanks in advance for your help

 

[ViroMajor]

Hope this will help

Hi James, thanks for your time ! Unfortunately, it also has no effect on Excel 365 (I’m sure it has on Excel 2013). It looks like a more drastic change of code is need, but I’m clueless about it

 

[ViroMajor]

actually, I should mention than I’m rather seeking when it “modifies/edits an existing comment” than add one... sorry about not having been precise enough in the first place

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...5-event-on-modifying-comment.html#post5102362

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ViroMajor]

Cross posted

Understood ! No worries, I always update to share when the solution is found...

 

[Jaafar Tribak]

@ViroMajor

You can use a timer to detect the moment a comment is added as well as the moment it is deleted .

1- Place this code in a Standard Module :

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
#End If


Sub StartTimer()
    Call StopTimer
    SetTimer Application.hWnd, 0, 0, AddressOf TimerProc
End Sub

Sub StopTimer()
    KillTimer Application.hWnd, 0
End Sub

Private Sub TimerProc()

    Static lCount As Long
    
    On Error Resume Next
    
    Call StopTimer
    If lCount < ActiveSheet.Comments.Count And lCount <> 0 Then
        ActiveCell.Select: Call ThisWorkbook.OnAddComment(ActiveSheet, ActiveCell)
    ElseIf lCount > ActiveSheet.Comments.Count Then
        ActiveCell.Select: Call ThisWorkbook.OnDeleteComment(ActiveSheet, ActiveCell)
    End If
    Call StartTimer
    lCount = ActiveSheet.Comments.Count
     'If GetActiveWindow <> Application.hWnd Then Call StopTimer
End Sub

2- And place this code in the ThisWorkbook Module :

Option Explicit

Private Const SHEET_NAME = "Sheet1" [COLOR=#008000][B]' Change this Cell as required.[/B][/COLOR]
Private Const CELL_ADDRESS = "$B$2" [COLOR=#008000][B]' Change this Cell as required.[/B][/COLOR]


Private Sub Workbook_Open()
    Call StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Call StopTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh Is Sheets(SHEET_NAME) Then
        If Target.Address = CELL_ADDRESS Then
            Call StartTimer
        Else
            Call StopTimer
        End If
    Else
        Call StopTimer
    End If
End Sub


[COLOR=#008000][B]' PSEUDO-EVENTS[/B][/COLOR]
[COLOR=#008000][B]'==============[/B][/COLOR]
Public Sub OnAddComment(ByVal Sh As Object, ByVal Target As Range)
    [D2] = "test réussi"
    MsgBox "You added a new Comment to cell : (" & Target.Address & ") in Sheet: (" & Sh.Name & ")"
End Sub

Public Sub OnDeleteComment(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You deleted a Comment in cell : (" & Target.Address & ") in Sheet: (" & Sh.Name & ")"
End Sub

 

[James006]

@ Jaafar ....

Très Bien Joué ...!!!

 

[ViroMajor]

Bonjour et merci ! j’avais zappé le sujet / thank you for this contribution, despite being late

For some reason, parts of the code are not working here

https://ibb.co/s9K8Zvw
https://ibb.co/hLY2JkT