Sub Comment_Add_Cell()
Dim cmt As Comment
Dim r As Range
Dim rr As Range
Set r = ActiveSheet.Range("A1")
Set rr = Sheets("Sheet2").Range("B1")
On Error Resume Next
r.Comment.Delete
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=rr.Text
End If
End Sub

Code for a range of cells will have to wait until after the hockey game.

Go Philly!!


Gord

On Mon, 24 May 2010 13:18:02 -0700, Gary McCarthy
wrote:

Gord,

Is there a way to do this referencing a cell on another tab? For example
Sheet2!B1 ?

Thanks.

"Gord Dibben" wrote:

Sub Comment_Add_Cell()
Dim cmt As Comment
Dim r As Range
Set r = Range("A1")
On Error Resume Next
r.Comment.Delete
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 1).Text
End If
End Sub

'For a range of cells in A with text in B

Sub Comment_Add_Range()
Dim cmt As Comment
Dim r As Range
For Each r In Range("A1:A10")
On Error Resume Next
r.Comment.Delete
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(0, 1).Text
End If
Next r
End Sub

Either of these you could call from a Workbook_Open event in Thisworkbook
module

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
Comment_Add_Range
'Comment_Add_Cell
End Sub


Gord

On Tue, 29 Jul 2008 12:38:01 -0700, NervousFred
wrote:

Gord your awesome, got it working how I want it.

One last question. I noticed, while trying to figure out the indexes for
Cells, that I had to delete the old comments first before the macro would
re-write the new comments over them.

Is there a delete comments line of code I could put ahead of the rest of the
code to make sure that everytime the macro is run it will put the new
information without me having to delete the old stuff first.

Also will this macro run automaticlly when the spreadsheet is opened? Just
trying to make it all autonomus without any user inputs.