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

chazrab

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,676
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
18,676
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top