Excel Comment shapes

What can you learn here ?
  • Interrupt comments
  • change shape characteristics
  • Add timestamp

Modifying the behavior of Cell Comment Processing  get it now

This article shows some techniques to interrupt the processing of cell comments in Excel so that you can modify the characteristics of the comments box programatically, for example adding a timestamp, or preserving the current contents of the cell as a comment. The functions mentioned are all included in the downloadable getting started workbook.

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

The class, cbEventsHandling looks like this, and will call tweakComment when the Insert Comment (or edit comment). For details of withevents properties like this see Dynamic Event Handlers or jSon and Dynamic Forms

Option Explicit
Public WithEvents cbarInsertComment As CommandBarButton

Private Sub cbarInsertComment_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    tweakComment ActiveCell
End Sub

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")
        With cBar
            Set cbEvent = New cbEventHandling
            Set cbEvent.cbarInsertComment = .Controls("Insert Comment")
        End With
    End If
    Exit Sub
    MsgBox ("could not initialize comment tweaking")
    Resume Next
End Sub

Private Sub Workbook_Open()
End Sub

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. 

Option Explicit

Public Sub tweakComment(target As Range)

    Const myName = "John Doe"
    Const delimiter = ":"
    Dim a As Variant, c As String, i As Long
     With target
        If .Comment Is Nothing Then
        End If
        With .Comment
            With .Shape
            ' mess around with the shape here (size,colors etc..)
                With .TextFrame.Characters
                    .Font.Color = vbBlue
                End With
            End With
            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
            End If
            .text myName & " at " & _
                Format(Now(), "dd-mmm-yy hh.mm") & delimiter & c
        End With
    End With

End Sub

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.