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
--
Regards,
Tom Ogilvy
"JT" wrote:
> 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.....
> --
> JT
|
Similar Threads
