comment copy&past!

R

riccifs

Hi to everyone,

I'm using this code to copy the text of active cell and paste it to
active cell comment.
I found it at this link:
http://groups.google.com/group/micr...96d834965e?lnk=gst&q=comment#2ddd1b96d834965e

It works very well but what I'd like to do is that:
a pop up form where I can chose to copy and past the cell content in
its comment but leaving than the cell empty.
The second choice of this pop up have to give to me the possibility to
disable the macro and to use the cell like another.

Hear is the 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

Many thanks to everyone want help me!
Bye, Stefano.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top