Excel Comment shapes

What can you learn here?

• Interrupt comments
• Change shape characteristics
• Add timestamp

Modifying the behavior of Cell Comment Processing


Download 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
handleErr:
    MsgBox ("could not initialize comment tweaking")
    Resume Next
End Sub
Private Sub Workbook_Open()
    initTweakComment
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
            .AddComment
        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)
            Else
                c = vbNullString
            End If
            .text myName & " at " & _
                Format(Now(), "dd-mmm-yy hh.mm") & delimiter & c
        End With
    End With
End Sub

Summary

This question was originally asked in the LinkedIn Excel Black belt group. Thanks to Andy Pope for his idea to hook into the commandbar to process cell comments. In the meantime why not take a look around the rest of the ramblings site, join our forum, follow the blog or follow me on twitter.

Continue reading about snippets.