What can you learn here?
- Interrupt comments
- Change shape characteristics
- Add timestamp
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
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.