displaying activecell comments in the same userform textbox or Listbox, updated as the activecell changes...

[chazrab]

...Is there any way to do this other than creating many separate Sheet locations to store unique comments?
Each time the user returns or moves to ANY cell with pre-entered comments, the Userform listbox or textbox would reflect those Comments for that particular cell, displayed in a list ?

The goal is to create a custom comments box(Userform) which changes dynamically with the activecell.

Thanks for anyone's help.
cr

 

[My Aswer Is This]

You said:

displayed in a list ?​

So are you wanting anytime you click on a cell with a comment you want that comment added to a Userform ListBox.

So if you click on Range("A1") which has a comment of "Dad" the value "Dad" will be added to the userform Listbox.

And then if you click on Range("B14") which has a comment on "Mom"

The userform listbox will now have "Dad" and "Mom"

So if that is what you want is there some particular range where these comments may be in?

Like just apply this to column(1) and Column (2)

Otherwise we will have to have the script always run when you click any cell on the sheet to see if there is a comment in that cell.

 

[My Aswer Is This]

If the answer to my question is yes and you only want to apply to Column(1) on the Active sheet.
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  3/10/2019  9:43:36 PM  EDT
If Target.Column = 1 Then
If Target.Comment Is Nothing Then MsgBox "No Comment": Exit Sub
UserForm1.ListBox1.AddItem Target.Comment.Text
End If
End Sub