How to: create a class that captures an event

HectorAM

New Member
Joined
Feb 6, 2022
Messages
2
Office Version
  1. 2007
  2. 2003 or older
Platform
  1. Windows
Since Excel doesn't catch an event based on a comment being changed or deleted, I'd like to know how to simulate it using a class. Thanks a lot
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi HectorAM
Here is a class object called "Comments"
VBA Code:
Private WithEvents myApp As Application
Dim CommentColl As Collection


Private Sub Class_Initialize()
    Set myApp = Application
    Set CommentColl = New Collection
    ProcessMyComments True
End Sub


Public Sub myApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ProcessMyComments False
    ProcessMyComments True
End Sub


Private Sub ProcessMyComments(Create As Boolean)
Dim sht As Worksheet
Dim cmt As Comment

For Each sht In ActiveWorkbook.Sheets
    For Each cmt In sht.Comments
        If Create Then
            CommentColl.Add sht.Name & "|" & cmt.Creator & "|" & cmt.Text
        Else
            CheckColl sht.Name & "|" & cmt.Creator & "|" & cmt.Text
        End If
    Next cmt
Next sht

End Sub

Private Function CheckColl(cmt As String) As String
    Dim Gone As Boolean: Gone = True
    For i = 1 To CommentColl.Count
        If CommentColl(i) = cmt Then
            Gone = False
        End If
    Next i
    If Gone Then MsgBox "Comment changed or deleted:" & vbNewLine & Split(cmt, "|")(0) & vbNewLine & Split(cmt, "|")(1) & vbNewLine & Split(cmt, "|")(2)
End Function
I put in worksheet code
VBA Code:
Dim cmt As Comments

Private Sub Workbook_Open()
    
    Set cmt = New Comments
End Sub
Now this doesn't work when someone deletes a comment yet but see what you think
 
Upvote 0
By the way, I hope this works in your version, from 2016 onwards you have to add a "Note" instead of a comment
 
Upvote 0
1- Add a new Class Module to your project and name it ClsComments :

Place the following code in the Class module:
VBA Code:
Option Explicit

Event OnCommentAdd(ByVal Cell As Range, ByRef Cancel As Boolean)
Event OnCommentDelete(ByVal Cell As Range, ByRef Cancel As Boolean)

Private WithEvents cmbrs As CommandBars
Private lCount As Long
Private oActiveCell As Range

Private Sub Class_Initialize()
    lCount = ActiveSheet.Comments.Count
    Set cmbrs = Application.CommandBars
End Sub

Private Sub cmbrs_OnUpdate()

    Dim bCancel As Boolean
    
    If lCount > ActiveSheet.Comments.Count Then
        RaiseEvent OnCommentDelete(ActiveCell, bCancel)
    End If
    
    If lCount < ActiveSheet.Comments.Count Then
        RaiseEvent OnCommentAdd(oActiveCell, bCancel)
    End If
    
    If bCancel Then
        Set cmbrs = Nothing
        Application.Undo
        Set cmbrs = Application.CommandBars
    End If
    
    lCount = ActiveSheet.Comments.Count
    Set oActiveCell = ActiveCell

End Sub


2- Add this code to the ThisWorkbook Module:
VBA Code:
Option Explicit

Private WithEvents ThisWorkbook As ClsComments

Private Sub Workbook_Open()
    Set ThisWorkbook = New ClsComments
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ThisWorkbook Is Nothing Then
        Set ThisWorkbook = New ClsComments
    End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set ThisWorkbook = New ClsComments
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set ThisWorkbook = Nothing
End Sub


'PSEUDO EVENTS
Private Sub ThisWorkbook_OnCommentAdd(ByVal Cell As Range, ByRef Cancel As Boolean)
    If MsgBox("A New Comment Was Added To Cell : " & Cell.Address(, , , True) & vbNewLine & vbNewLine & _
    "Do You Want To Remove the Newly Added Comment ?", vbQuestion + vbYesNo) = vbYes Then
        Cancel = True
    End If
End Sub

Private Sub ThisWorkbook_OnCommentDelete(ByVal Cell As Range, ByRef Cancel As Boolean)
    If MsgBox("The Comment In Cell : " & Cell.Address(, , , True) & " Has Been Removed." & vbNewLine & vbNewLine & _
    "Do You Want To Restore the Deleted Comment ?", vbQuestion + vbYesNo) = vbYes Then
        Cancel = True
    End If
End Sub
 
Upvote 0
Thank you very much for your quick responses. Good job
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top