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.
Option Explicit Public WithEvents cbarInsertComment As CommandBarButton Private Sub cbarInsertComment_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) tweakComment ActiveCell End Sub
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
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)
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.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines