Log In
Register
Home
Dashboards
Power Query
October 28, 2021
Offline
Please Note : I have also posted this question on LinkedIn (Excel Data Analyst Group)
Any help solving this issue would be greatly appreciated...
Table Name = ORDER1
I would like to add the cell contents from column 'Comments' into a comment (Red Triangle that appears in top right of cell when hovered over) in Column 'Operation' for each row in the table.
Ideally the comment box would auto resize dependent on comment length.
Any Ideas ???
Have a good weekend everyone.
Andy
Trusted Members
Moderators
November 1, 2018
Offline
Essentially the code would be something like:
sub AddCommentsToOperations()
application.screenupdating = false
dim myTable as listobject
set mytable = activesheet.listobjects("ORDER1")
dim rw as long
for rw = 1 to mytable.listrows.count
dim cell as range
set cell = mytable.listcolumns("Operation").databodyrange.cells(rw)
if not cell.comment is nothing then cell.comment.delete
cell.addcomment mytable.listcolumns("Comments").databodyrange.cells(rw).value
next rw
end sub
Dashboards
Power Query
October 28, 2021
Offline
Thanks Velouria,
I will give it a try.
Andy
Dashboards
Power Query
October 28, 2021
Offline
Hi Velouria,
Hope you’re enjoying the weekend and many thanks for you’re help.
im just going to test your code to see if it works and solves my problem.
what type of code selection do i need to make when entering the code.
Worksheet & deceleration ****
Is it tableupdate as my data is in a table not a pivot table
best Regards
andy
Trusted Members
Moderators
November 1, 2018
Offline
It depends - what do you want to trigger the code?
Dashboards
Power Query
October 28, 2021
Offline
Hi Velouria,
I hope you are well,
You're code below is working brilliantly.
sub AddCommentsToOperations()
application.screenupdating = false
dim myTable as listobject
set mytable = activesheet.listobjects("ORDER1")
dim rw as long
for rw = 1 to mytable.listrows.count
dim cell as range
set cell = mytable.listcolumns("Operation").databodyrange.cells(rw)
if not cell.comment is nothing then cell.comment.delete
cell.addcomment mytable.listcolumns("Comments").databodyrange.cells(rw).value
next rw
end sub
Quick question
How would I amend the code to do the same function with the comments on multiple tables in the same worksheet.
All tables have the same column names etc.. it's just the table names that are different - example table names ORDER1_2, ORDER1_3
Best Regards
Andy
Trusted Members
Moderators
November 1, 2018
Offline
Do you want it to do every table on the sheet? If so, something like:
sub AddCommentsToOperations()
application.screenupdating = false
dim myTable as listobject
for each myTable in activesheet.listobjects
dim rw as long
for rw = 1 to mytable.listrows.count
dim cell as range
set cell = mytable.listcolumns("Operation").databodyrange.cells(rw)
if not cell.comment is nothing then cell.comment.delete
cell.addcomment mytable.listcolumns("Comments").databodyrange.cells(rw).value
next rw
next mytable
end sub
1 Guest(s)
