Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Linking Workbooks to Get Cell Comments

0

HI

I am looking to link a workbook to another. But I need to be able to read the comments from the original workbook in the new one. Also I'd like to have the formats displayed too.

Any suggestions

Post Edited
Title: Title was not descriptive.
Answer
Discuss

Discussion

I have used this code but it doesnt always work..... do I need to have both workbooks open and....

The source workbook is in read only (ie being edited by someone else) will this be an issue?

At present I have both work books open (source one in read only) and the showcomment is not working...just shows #value?.... no longer seeing showcomment as a function....
simont485 (rep: 2) Mar 26, '19 at 5:04 am
Add to Discussion

Answers

0
Selected Answer

Hi there!

You can't do this directly. I mean, you can copy/paste the cells and the comments will transfer but there will be no linking.

A workaround to this is to output the comments from the first workbook into a column and then link to that column.

Or, you can simply use a custom formula to point to the cells that have comments in the first workbook and output them in the second workbook.

Either way, the comments will end up being output into the spreadsheet itself in the second workbook. The only workaround is VBA, which is probably overkill and prone to errors when it comes to working with specified external workbooks, though it's still possible.

The easiest solution could look like this:

Install this UDF into your workbook where you want the data to appear:

Function showComment(cell As Range) As String

' Cause an empty cell to appear instead of an error.
On Error Resume Next

' Outputs the comment from the selected cell, if it exists.
showComment = cell.Comment.Text

End Function

Just hit Alt + F11 and in the VBA window go to Insert > Module and paste the code into there.

Then, go back to the worksheet and type the formula showComment( into the cell where you want the comment to appear and navigate to the worksheet where the comments appear and select the first cell in the column and hit enter.

Now, you can copy this formula down the column in the destination workbook and it will show any comments that it finds from the selected cells in the source workbook.

This may seem a bit confusing but give it a shot and let me know how it goes!

Discuss

Discussion

OK that works in as much as it displays the comment in a cell. Which is good as I can use that cells contents with conditional formating in my linked cell(s)

So many thanks.
simont485 (rep: 2) Mar 22, '19 at 6:01 am
Glad to help! :)
don (rep: 1989) Mar 22, '19 at 6:59 am
I have used this code but it doesnt always work..... do I need to have both workbooks open and....

The source workbook is in read only (ie being edited by someone else) will this be an issue?

At present I have both work books open (source one in read only) and the showcomment is not working...just shows ##### value?....
simont485 (rep: 2) Mar 26, '19 at 4:26 am
Which way did you implement this? Try putting the custom formula in the worksheet that has the comments and then simply using a regular formula to link to the cell that now contains the comment. You could also hide the column to make it so the original workbook doesn't have a column that confuses people.
don (rep: 1989) Mar 27, '19 at 5:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login