Command Bar event associated with Excel Comment
To do this, we are going to create a class, and WithEvents properties, as described in Dynamic Event Handlers
. When you 'Insert Comment', this class is going to allow us to modify the properties of the comment shape programatically.
Creating the Class
Public WithEvents cbarInsertComment As CommandBarButton
Private Sub cbarInsertComment_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Initializing the event
We want to make workbook wide behavior, so we are going to set up the event handling of insert comments in the ThisWorkbook module, so that it executes when the workbook opens. So in ThisWorkbook, add this..
Dim cbEvent As cbEventHandling
Public Sub initTweakComment()
' run this on worksheet activate
Dim cBar As CommandBar
On Error GoTo handleErr
If cbEvent Is Nothing Then
Set cBar = Application.CommandBars("Cell")
Set cbEvent = New cbEventHandling
Set cbEvent.cbarInsertComment = .Controls("Insert Comment")
MsgBox ("could not initialize comment tweaking")
Private Sub Workbook_Open()
Processing the event
As a result of this setup, our cbeventhandling class will be activated every time an insert or edit comment selection is made, so all that remains is to process the event. In some module, create the tweakComment procedure as follows. In this case, I'm preserving any comments that are there, changing the default name in the comment box, adding a time stamp and changing the font color. You can do all sorts of things here, including changing the shape characteristics, making the comment permanently visible etc.
Public Sub tweakComment(target As Range)
Const myName = "John Doe"
Const delimiter = ":"
Dim a As Variant, c As String, i As Long
If .Comment Is Nothing Then
' mess around with the shape here (size,colors etc..)
.Font.Color = vbBlue
c = .text
' preserve any comments other than intro
a = Split(c, delimiter)
If LBound(a) < UBound(a) Then
c = Mid(c, Len(a(LBound(a))) + 2)
c = vbNullString
.text myName & " at " & _
Format(Now(), "dd-mmm-yy hh.mm") & delimiter & c