Making a comment using a cell?

[NervousFred]

Is there a possible way to make a comment be the information from another cell?

I am importing data from another program and the data is in A1 and the
comments are written out in B2. Is it possible to make the comments of A1 be
the same as B2 without copying it directly? (Using forumlas/code)

Thanks!

 

[Brad]

Something like

Sub UpdateValidation()
Range("IssDate").Validation.ErrorMessage = "Issue Date has to be between
" & _
Range("InpStartDate").Value & " and " & Range("InpEndDate").Value
End Sub

However the .Validattion.ErrorMessage - would have to be changed to identify
the cell comment.

 

[Gord Dibben]

You could use a macro.

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
Set r = Range("A1")
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()
Dim cmt As Comment
Dim r As Range
For Each r In Range("A1:A10")
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


Gord Dibben MS Excel MVP

 

[NervousFred]

Gord,

I understand most of your code but one thing I am missing is where the input
for the comment is. I see that it puts the comment on A1 but just don't see
where I can change it to make the commenet have B1's data in it.

I am somewhat code illiterate for MS Office.

 

[Gord Dibben]

Your original post asked for A1 Comment to be the text from B2

Is it possible to make the comments of A1 be
the same as B2 without copying it directly? (Using forumlas/code)

Hence the cmt.Text Text:=r.Offset(1, 1).Text which is B2

For B1 change to cmt.Text Text:=r.Offset(0, 1).Text


Gord

 

[NervousFred]

Ahhh...thanks!

Does it work like a coordinate system? so C2 would be (2,1)? Or does it just
move it (0,1) from the orginal cell(A1).

 

[Gord Dibben]

There is a difference when using Offset or using cell coordinates

Offset is the cell address Offset(rowindex, columnindex) from whatever cell
is the activecell.

Not using the Offset, the coordinates of C2 would be Cells(2, 3)

Which is Cells(rowindex, columnindex) and always from top left of sheet.


Gord

 

[NervousFred]

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.

 

[Gord Dibben]

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

 

[NervousFred]

I put
Private Sub Workbook_Open()
Sheets("Sheet1").Activate
Comment_Add_Range
End Sub

in module 1 and opened the spreadsheet and it did not run the macro
automatticlly. Also tried puttin the code right above and below the orignal
commnet code. Neither worked and I am sure I am doing something wrong.

Where do I need to put the activate code to get the macro to run?

 

[Gord Dibben]

Quoted from my prior post...............

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

End quote.......................

So...................................

The Workbook_Open code goes into Thisworkbook module.

The Comment_Add_Range macro goes into a General Module


Gord

 

[NervousFred]

Works great, Thanks so much for your help gord, sorry I am slow.

 

[Gord Dibben]

No problem Fred

Glad to help.


Gord

 

[PSS]

I used this macro in Excel 2003. It worked until I change the
comments...the old comments remain in the comment box even when
I run the macro, save, close and reopen. Any advice? I started with a
small scale since I am new at attempting macros.
Here is what I used:

Sub Comment_Add()
Dim cmt As comment
Dim r As Range
For Each r In Range("A2:A4")
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

 

[Gord Dibben]

You mean when you change the text in any cell in B2:B4 the Comment does not
change when you run the macro?

It won't, because it already has a Comment so the macro skips that cell or
cells.

Try this revision which will change the text in existing Comments and add a
Comment if there is none.


Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Range("A2:A4")
Set cmt = r.Comment
If Not cmt Is Nothing Then
cmt.Text Text:=r.Offset(0, 1).Text
Else
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(0, 1).Text
End If
Next r
End Sub


Gord

 

[PSS]

Worked like a charm. Thanks so much!
--
J~

You mean when you change the text in any cell in B2:B4 the Comment does not
change when you run the macro?

It won't, because it already has a Comment so the macro skips that cell or
cells.

Try this revision which will change the text in existing Comments and add a
Comment if there is none.


Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Range("A2:A4")
Set cmt = r.Comment
If Not cmt Is Nothing Then
cmt.Text Text:=r.Offset(0, 1).Text
Else
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(0, 1).Text
End If
Next r
End Sub


Gord

 

[Gord Dibben]

Happy to help

Gord

 

[Gary McCarthy]

Gord,

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

Thanks.

 

[Gord Dibben]

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