Excel 2007 Comment Help

[Barda]

Hi.. I am looking for a VBA code to automatically insert the value of the "B" cell in the active row in my comment. I am using the double click to insert comment VBA code right now, but I would also like it to insert the value from the B column in the row of the cell that I am double clicking to insert my comment and add that value to the comment itself.

From my understanding I cannot change the cell address (i.e. "Cell: G10" when I have the print comment feature selected. I do not want to print the grid lines or row/column labels so I have no way to reference what "G10" may be when it is printed. So I thought I could put a prefix on the comment to reference the cell where it came from.

Note: The cell that I am referencing in the B column is actually a date (not the current date) so a date stamp wouldn't work.

Any help would be greatly appreciated.

[Chabu]

I could be wrong but it seems to me that macro execution is not possible when in edit mode on a comment.

[Paul_Hossler]

maybe something like this. Combine the Comment text you want to enter with reading the date in col B, and then putting both into the comment



Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rAddCommentHere As Range, rAddData As Range
Dim sComment As String

Set rAddCommentHere = Target.Cells(1, 1)

sComment = InputBox("Enter the comment")

If Len(sComment) = 0 Then Exit Sub


Set rAddData = rAddCommentHere.EntireRow.Cells(1, 2)

With rAddCommentHere
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=sComment & vbLf & vbLf & "The Date from column B was " & rAddData.Value
End With

End Sub



Paul

[Barda]

My overall objectiveis to print comments at the end of the sheet that display the defined cell name instead of the cell number.

For example, I would like my print out to display...

Cell: "Defined Name"
Comment:

Instead of...

Cell: B2
Comment:

Is this possible?

Thanks for the help!