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!

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.
--
Wag more, bark less


"NervousFred" wrote:

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!

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

On Fri, 25 Jul 2008 13:37:03 -0700, NervousFred
wrote:

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!

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.

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

On Tue, 29 Jul 2008 08:53:01 -0700, NervousFred
wrote:

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.

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" wrote:

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

On Tue, 29 Jul 2008 08:53:01 -0700, NervousFred
wrote:

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.

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

On Tue, 29 Jul 2008 11:45:03 -0700, NervousFred
wrote:

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" wrote:

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

On Tue, 29 Jul 2008 08:53:01 -0700, NervousFred
wrote:

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.

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

--
Thank you in advance,
J~


"Gord Dibben" wrote:

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

On Fri, 25 Jul 2008 13:37:03 -0700, NervousFred
wrote:

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!

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

On Thu, 8 Jan 2009 04:56:01 -0800, PSS
wrote:

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

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


"Gord Dibben" wrote:

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

On Thu, 8 Jan 2009 04:56:01 -0800, PSS
wrote:

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

Happy to help

Gord

On Fri, 9 Jan 2009 04:23:03 -0800, PSS
wrote:

Worked like a charm. Thanks so much!