- Interrupt comments
- Change shape characteristics
- Add timestamp
Modifying the behavior of Cell Comment Processing
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) 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.