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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
579
...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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,823
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,823
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top