Clever comment box

[jamessaunders]

Hello all,

Is there a way......

I would like a comment box to appear in a cell, repeating the contents of another cell, if the cell value is greater than 0.

i.e.

In cell AQ4 I have a sumproduct that counts the amount of times a date appears in a list in another worksheet.

If the total is greater than 0 (dates are found), the cell changes colour - great!

Now I would like to add a comment that displays the names that are associated with the dates found in the sumproduct of cell AQ4.

Hmmm... is this possible?

[Ikaabod]

This bit can create or delete a comment box depending on whether AQ4 is greater than zero or not.

Sub CommentBoxTest()
    On Error Resume Next
    Dim strNames As String
    strNames = "Joe, Bob, Jim, Steve" 'or whatever code you use to figure out what text should be displayed in the comment box.
    If Range("AQ4") > 0 Then
      Range("AQ4").Comment.Delete
      Range("AQ4").AddComment
      Range("AQ4").Comment.Text Text:=strNames
      Range("AQ4").Comment.Visible = True 'Set equal to False if you want the comment to hide itself when cell is not selected.
    Else
      Range("AQ4").Comment.Delete
    End If
End Sub

As for the text inserted, more information would be required, but initially it sounds like it's feasible.

[jreimer]

I'm looking for something similar to this, but only to copy the contents a specific cell into a comment of another cell.

Would this code work?