Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Here is how you can automatically update a comment that shows unique items in sorted order from the larger worksheet list, whenever a new unique item is added to that list in the worksheet.

In the pictured example, a company keeps an ongoing list of its clients and dates of transactions. When a new client is added to the list, such as what is happening in cell A20, the comment in cell A1 gets updated automatically, to show that new client name in a freshly sorted list.



To do this, for the worksheet where this is happening, right-click the worksheet tab and left-click to select View Code, as shown in the next picture. This will take you to the Visual Basic Editor (VBE), directly to the module for that worksheet.



Next, copy and paste the below Worksheet_Change event procedure into the large white pane that is the worksheet module.



Finally, exit the VBE and return to the worksheet by pressing Alt+Q.



Here’s the Worksheet_Change procedure, with explanatory comments in green font.

Private Sub Worksheet_Change(ByVal Target As Range)

'Limit the event to monitor only changes in column A.
If Target.Column <> 1 Then Exit Sub

'Prepare Excel's application settings.
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

'Declare variables.
Dim HelperColumn As Long, cell As Range, strCommentText As String

'Define the helper column which is the last used column + 2,
'to use for listing the unique client names and sorting them.
HelperColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 2

'List the unique client names in the helper column.
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(1, HelperColumn), Unique:=True

'Sort the unique client list in ascending order.
Cells(1, HelperColumn).Sort _
Key1:=Cells(2, HelperColumn), _
Order1:=xlAscending, _
Header:=xlYes

'Build the comment's text string, comprised by each unique client name
'in a vertical list. To do that, separate each name with the ascii 10
'carriage return character.
strCommentText = ""
For Each cell In Cells(1, HelperColumn).CurrentRegion
'Bypass the header cell in row 1.
If cell.Row <> 1 Then _
strCommentText = strCommentText & Chr(10) & cell.Value
Next cell
strCommentText = "Unique client names:" & Chr(10) & strCommentText

'You are maintaining your comment in cell A1 that lists the unique
'client names whenever a new one is added to column A in the table.
With Range("A1")
If Not .Comment Is Nothing Then .Comment.Delete
.AddComment
With .Comment
.Visible = False
.Text Text:=strCommentText
.Shape.TextFrame.AutoSize = True
End With
End With

'Clear the helper column's unique list which now is represented
'in the comment.
Columns(HelperColumn).Clear

'Reset Excel's application settings.
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub