Place Comment into a cell

[terryv]

Hello
Is there anyway to take a comment from a cell and place it in another cell's
content?

If a comment is added to a cell on Sheet1, after the comment is closed
(finished), I would like to have the cell content of another sheet be the
comment content.

Kinda like a OnChange for a comment.

Thank you
Terry

 

[Frank Kabel]

Hi
AFAIK this is not possible as a comment change does not trigger any
event

 

[Jim May]

try the UDF:

Function MyComment(rng As Range)
Application.Volatile
Dim str As String
str = Trim(rng.Comment.Text)
'// Next line removes Chr(10) character from string
str = Application.Substitute(str, vbLf, " ")
MyComment = str
End Function

Then with your cell a4 including a comment, in cell B5 enter
=MyComment(A4)

HTH

 

[terryv]

Hey Jim
Thank a million it works great.... exactly what I needed....
Anytime I do anything on my worksheets though, it takes a long time to
recalculate the sheets .... I see it calculating on the bottom left. Is
there a way to speed this up?

Thanks Jim
Terry V

 

[Jim May]

Here's another version:

Sub tester()
x = ActiveCell.Comment.Text
mycell = Application.InputBox("To What Cell Do I Copy the Comment?")
Range(mycell).Value = x
End Sub

 

[David McRitchie]

Hi Terry,
Another way would be to remove the Volatile line from the code.
then do your own Ctrl+Alt+F9 to recalculate all worksheets

To automate that you could use an Event macro that is triggered by
some other event, because as Frank indicated there is no event macro
for changing a comment. This will cause the recalculation to occur
whenever you second sheet with the formulas is activated, the macro
would normally not just have one line in it. The double click event macro
simply activates the worksheet activate macro so you use the same code,
but can see the effect without changing worksheets.

Right click on the worksheet tab, view code and insert the following
after your Option Explicit

Private Sub Worksheet_Activate()
'-- in use to avoid use of volatile
Application.CalculateFull ' ctrl-alt-f9
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True 'get out of edit mode from DoubleClick
Worksheet_Activate
End Sub


Read more about this at and Event macros at
http://www.mvps.org/dmcritchie/excel/event.htm#recalculate

 

[Jim May]

David - Thanks for the response;
on your site (referenced) under your explanation of Worksheet_Activate Event
you use the language "when the sheet is opened".. could I interpret this
(event) as
being "when the sheet becomes the current sheet (receiving the focus (of the
cursor))"?
Sorry to split hairs, but until I do, I often find myself up-in-the-air...
Tks,
Jim

 

[David McRitchie]

Hi Jim,
Moving the cursor on a worksheet would be
Worksheet_SelectionChange event. Selecting a different sheet
is Worksheet_Activate event. So the answer to your
question about moving the cursor focus by itself is not
worksheet activation.

When a workbook is opened all cells are recalculated.
So you would not have any problems in that respect.

When a sheet tab is selected by itself that sheet is activated.

Simply being part of a grouped sheets selection and moving
the cursor does not activate the sheet.

If you select the same sheet as is already active, or move the
cursor around you are not activating the worksheet; therefore,
your change to the comment would not be picked up yet.

If you switch sheets and return the Worksheet_Activate event
will be triggered and the cell referencing the cell comment would
be updated. Likewise, if you simply double click that event
was set up to trigger the Worksheet_Activate event.

Removing Volatile and using the two event macros
is not foolproof, the user should be told how this works.
If they forget, chances are they will be selecting other sheets
and returning. If not it will still be corrected when the workbook
is reopened.

BTW, I also updated my ccomment.htm page to refer to the event.htm
page for the mycomment macro. But because of the use of volatile
I never did have it in the code/ccomment.txt page -- though I did add
it now as commented out.

 

[Jim May]

When a sheet tab is selected {either by clicking on the tabname or by
using the keyboard combination Control-PageUp, or Control-PageDown] that
sheet is then activated.

Got it!! Thanks David
Jim May