Copy comment to another cell

[Guest]

On the "Data List" worksheet, the user right-clicks on any column on the row
that contains the data they want to process.

My code picks up several pieces of information and writes it on the
"Worksheet" worksheet.

So far, so good.....However......

Column K on the "Data List" worksheet may or may not contain a comment for
the specific item.

If a comment exists, I would like to: (1) Add the commet to cell (K9) on the
"Worksheet" worksheet and (2) Add "Yes" to cell (K9) on the "Worksheet"
worksheet also.

If there is no comment then I would like to add "No" to cell(K9) on the
"Worksheet" worksheet.

I've seen code that will copy all of the comments on one sheet to another
and have tried to modify that to do what I want but I'm having trouble with
that. Any suggestions or code to accomplish this would be greatly
appreciated.

Thanks for the help.....

 

[Guest]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Count > 1 Then Exit Sub
Dim cell As Range, Cell1 As Range
Dim cmt As Comment
Set Cell1 = Worksheets("Worksheet").Range("K9")
Set cell = Worksheets("Data List").Cells(Target.Row, "K")
If cell.Comment Is Nothing Then
Cell1.Value = "No"
If Not Cell1.Comment Is Nothing Then
Cell1.Comment.Delete
End If
Else
Cell1.Value = "Yes"
If Cell1.Comment Is Nothing Then
Set cmt = Cell1.AddComment
cmt.Text Text:=cell.Comment.Text
Else

Cell1.Comment.Text Text:=cell.Comment.Text
End If
End If
Cancel = True
End Sub

 

[Guest]

Tom........Thanks for the help........
--
JT

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Count > 1 Then Exit Sub
Dim cell As Range, Cell1 As Range
Dim cmt As Comment
Set Cell1 = Worksheets("Worksheet").Range("K9")
Set cell = Worksheets("Data List").Cells(Target.Row, "K")
If cell.Comment Is Nothing Then
Cell1.Value = "No"
If Not Cell1.Comment Is Nothing Then
Cell1.Comment.Delete
End If
Else
Cell1.Value = "Yes"
If Cell1.Comment Is Nothing Then
Set cmt = Cell1.AddComment
cmt.Text Text:=cell.Comment.Text
Else

Cell1.Comment.Text Text:=cell.Comment.Text
End If
End If
Cancel = True
End Sub