Comment (copy text of activecell & paste to activecell comment)

[Edmund Seet]

VBA rookie here. Is the below do-able?

(a) I want to copy the content of activecell(Text only),
then paste that string as Comment into the ActiveCell
itself. Example:
ActiveCell Content = "Edmund wrote : PO has been
rescheduled, status ok." Get VBA to copy this string & put
into the activecell as a Comment.

(b) I also need this VBA to consider, IF A1 already
contain comment, then put this newly copied comment under
the previous comment. Yes, everytime, if Excel finds that
the activecell contain comment(s), pls put the new comment
under the last one.

(c) If possible, I need excel to put the "date & time" to
the on top of the each comment when they are copied from
the activecell & pasted as comment.

Example of how comment should look in ActiveCell.

dd/mm/yyyy hh:mm AM/PM
User wrote: text...text....text of activecell.
17/04/2003 12:44 PM
User wrote : PO has been rescheduled, status ok."
17/04/2003 1:22 PM
User wrote: PO has been pushed out.Vendor refused
17/04/2003 3:55 PM
User wrote: Miracle ! Vendor allow push out.
[and it goes on & on...when macro is run each time]

Thank your very much for your help & guidance.

Edmund Seet

[Dave Ramage]

Edmund,

Here you go...

Sub AddCellTextToCellComment()
Dim strExistingText As String, strNewText As String

With ActiveCell
strNewText = Format(Now, "dd/mm/yyyy hh:mm AM/PM")
& Chr(10) & .Value
On Error Resume Next
strExistingText = .Comment.Text 'triggers error if
no comment
On Error GoTo 0

If strExistingText = "" Then
'no existing comment, so add new
.AddComment strNewText
Else
'is existing comment
.Comment.Text strExistingText & Chr(10) &
strNewText
End If

'sort out comment size- cannot do directly, so
longwinded way
.Comment.Visible = True
.Comment.Shape.Select
Selection.AutoSize = True
.Comment.Visible = False
End With
End Sub

Careful how the lines copy- sometimes you get an extra
charriage return depending on your browser.

Not sure how you are planning to run this macro, but if
you want it done automatically every time a certain cell
(or range of cells) is changed, then use the
Worksheet_Change event. E.g. add the following code to the
appropriate worksheet code moduel (right-click worksheet
tab and select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
'Target is a reference to the range that was changed
Const cCheckRange = "A1:J10" 'define range in which this
is active
Dim rngR As Range
Dim strExistingText As String, strNewText As String

For Each rngR In Target 'Target may have more than one cell
'is it part of the desired range?
If Range(cCheckRange).Address = Union(Range
(cCheckRange), rngR).Address Then
With rngR
strNewText = Format(Now, "dd/mm/yyyy hh:mm
AM/PM") & Chr(10) & .Value
On Error Resume Next
strExistingText = .Comment.Text 'triggers
error if no comment
On Error GoTo 0

If strExistingText = "" Then
'no existing comment, so add new
.AddComment strNewText
Else
'is existing comment
.Comment.Text strExistingText & Chr(10) &
strNewText
End If

'sort out comment size- cannot do directly, so
longwinded way
.Comment.Visible = True
.Comment.Shape.Select
Selection.AutoSize = True
.Comment.Visible = False
End With
End If
Next rngR
End Sub

Cheers,
Dave.

>.
>

[Edmund Seet]

Once again, I'm convinced that Excel Newsgroup is filled
with extremely kind & helpful people.

Being a VBA rookie, I will need months in learning before
I could even write something close to this, if it ain't
for Dave Ramage.

Thank you so very much for taking the trouble in going
the extra mile.

Edmund Seet