Excel: Automatic updates of Comment boxes

[BeckyEllerby]

*Is it possible to link and Automatically update information betwee
two comments boxes*

Problem Description:

The problem is i run our comapnies finance ledgers in Excel and i a
trying to create/share information between two workbooks. Worksheet on
contains information, and is updated with information, that workshee
two also needs.

Therefore I have been linking cells in worksheet 2 with the relevan
cells in worksheet 1 via formulas within the cells. However some of th
information that i need to link up i cannot do via cell formulas . Th
reason i cannot do this is that this information is importan
information or notes, i make regarding particlur cells, and ca
sometimes be a quite lengthy, it does not need to be visable in th
second worksheet but it needs to be easily available, and linking th
large amount of information in cells causes too much disruption t
worksheet 2 especially when it does not need to be permenantl
visable.

To date i have been sharing information between the two worksheets i
two ways:
1. the information that needs to be permanently visable has bee
linked via the cells by using formulas
2. the information/notes relating to paricular cells are entered int
worksheet one as a comment (linked to the appropriate cell) and the
copied and pasted into worksheet two's corresponding cell.

However the problem is that while the information in the cells o
worksheet two are automatically updated with any changes in workshee
1, the information in the comments boxes are not automatically updated
therefore regular copying and pasteing has been the only solution t
date but this is huge job.

Therefore i would like to find a solution / way of having this cel
related information readly available but without it disrupting th
worksheet. Problems with options found so far are:
1. Comments box
does not automatically update but is excellent at holding large amoun
of information and is
easily accessed by hovering over a cell and it also does not disrup
the worksheet
2. Text boxes with links to information in worksheet one
do automatically update but dont hide like comments do so they disrup
the worksheet when
there is a lot of information
3. Information linked within cells
causes disruption to worksheet as amount of information can sometime
be large but does
automatically update
4. Information linked within cells but then hiding cells
as above but information is not easily accessed as with comments boxe

 

[Frank Kabel]

Hi
problem is I don't see a way to 'link' comments to each other. There's
(AFAIK) no event which gets triggered by inserting a comment. So even
an event procedure won't work.
So you may have to life with one of the drawbacks (i would use option
4)

 

[Jim Cone]

Becky,

As Frank pointed out there is no built in or automatic way to do what you want.
However, you can run VBA code to accomplish it.

In my limited testing, the updated comments did not have bold font?
Also, comments are assumed to be in identical cell locations in the two books.

Make backup copies of your workbooks before trying this.
Attach the following code routine to a button from the Forms toolbar.
Both workbooks must be open and on the same computer.
Paste the following into a general module, edit it with the actual workbook
and worksheet names. (Lines prefaced with an " ' " are not used)...

'-----------------------------
Sub CoordinateComments()
Dim objCmt As Comment
Dim strCmtAddress As String

For Each objCmt In Workbooks("OriginalBook.xls").Worksheets("Sheet1").Comments
strCmtAddress = objCmt.Parent.Address

' The following copies everything in the cell to the other workbook.
' Range(strCmtAddress).Copy Destination:= _
' Workbooks("SimiliarBook.xls").Worksheets("Sheet1").Range(strCmtAddress)
' or
' The following two lines only affect the comment, the cell contents are left
alone.

Workbooks("SimiliarBook.xls").Worksheets("Sheet1").Range(strCmtAddress).Comment.
Delete

Workbooks("SimiliarBook.xls").Worksheets("Sheet1").Range(strCmtAddress).AddComme
nt objCmt.Text
Next
Set objCmt = Nothing
End Sub
'-----------------------------

Regards,
Jim Cone
San Francisco, CA

*Is it possible to link and Automatically update information between
two comments boxes*
Problem Description:
The problem is i run our comapnies finance ledgers in Excel and i am
trying to create/share information between two workbooks. Worksheet one
contains information, and is updated with information, that worksheet
two also needs.
Therefore I have been linking cells in worksheet 2 with the relevant
cells in worksheet 1 via formulas within the cells. However some of the
information that i need to link up i cannot do via cell formulas . The
reason i cannot do this is that this information is important
information or notes, i make regarding particlur cells, and can
sometimes be a quite lengthy, it does not need to be visable in the
second worksheet but it needs to be easily available, and linking the
large amount of information in cells causes too much disruption to
worksheet 2 especially when it does not need to be permenantly
visable.
To date i have been sharing information between the two worksheets in
two ways:
1. the information that needs to be permanently visable has been
linked via the cells by using formulas
2. the information/notes relating to paricular cells are entered into
worksheet one as a comment (linked to the appropriate cell) and then
copied and pasted into worksheet two's corresponding cell.
However the problem is that while the information in the cells on
worksheet two are automatically updated with any changes in worksheet
1, the information in the comments boxes are not automatically updated,
therefore regular copying and pasteing has been the only solution to
date but this is huge job.

- snip -

 

[BeckyEllerby]

Thank you for your replies.

I've never used a VBA before but i will give it a shot

 

[Jim Cone]

Becky,

Thanks for letting us know.
Note - watch out for word wrap in the code.
Let us know if you have more questions.

Regards,
Jim Cone