Making a comment using a cell?
 

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!

Making a comment using a cell?
 

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!

Making a comment using a cell?
 

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!

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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?

Making a comment using a cell?
 

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

On Thu, 31 Jul 2008 07:41:08 -0700, NervousFred
wrote:

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?

Making a comment using a cell?
 

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

Making a comment using a cell?
 

No problem Fred

Glad to help.


Gord

On Thu, 31 Jul 2008 10:44:01 -0700, NervousFred
wrote:

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

Making a comment using a cell?
 

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!

Making a comment using a cell?
 

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

Making a comment using a cell?
 

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

Making a comment using a cell?
 

Happy to help

Gord

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

Worked like a charm. Thanks so much!

Making a comment using a cell?
 

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.

Making a comment using a cell?
 

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.