Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

You can try copying the row of comments and choosing the Edit, Paste Special,
Comments command to paste the comments into the other row.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Michael.Tarnowski" wrote:

Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?


Gord Dibben MS Excel MVP


On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"
wrote:

Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

A little late getting back to you.

Try this revision which updates the Comments' contents.

Sub Comment_Add()
Dim addtext As String
Dim cmt As Comment
Dim rng As Range
Set rng = Sheets("Sheet2").Range("A8:M8")
For Each cell In rng
Set cmt = cell.Comment
addtext = cell.Offset(1, 0).Text
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Text Text:=addtext
Else
cmt.Text Text:=addtext
End If
Next cell
End Sub

To be truly dynamic, you could call it from some type of worksheet event
like calculate.

Private Sub Worksheet_Calculate()
Comment_Add
End Sub

The event code would be inserted into the Sheet2 module and would trigger
when you made a change in any of the row 9 source cells in Sheet1.


Gord


On Sun, 1 Feb 2009 06:26:01 -0800 (PST), "Michael.Tarnowski"
wrote:

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

On Feb 2, 6:14 pm, Gord Dibben <gorddibbATshawDOTca wrote:
A little late getting back to you.

Try this revision which updates the Comments' contents.

Sub Comment_Add()
Dim addtext As String
Dim cmt As Comment
Dim rng As Range
Set rng = Sheets("Sheet2").Range("A8:M8")
For Each cell In rng
Set cmt = cell.Comment
addtext = cell.Offset(1, 0).Text
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Text Text:=addtext
Else
cmt.Text Text:=addtext
End If
Next cell
End Sub

To be truly dynamic, you could call it from some type of worksheet event
like calculate.

Private Sub Worksheet_Calculate()
Comment_Add
End Sub

The event code would be inserted into the Sheet2 module and would trigger
when you made a change in any of the row 9 source cells in Sheet1.

Gord

On Sun, 1 Feb 2009 06:26:01 -0800 (PST), "Michael.Tarnowski"
wrote:

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

Gord,
thank you for the extention to a dynamic version. It works - but in
the wrong direction ;-(
I assume I mixed something up so you did not get the point.
To clarify my intention:

Sheet "Configuration" helds in A8:AF8 the comment text - since this
row may expands I defined a dynamic range "dynComments" for this
In sheet "ActionItemList" the cells in row a9:AF9 shall get the
comment of each cell in dynComments.

Your code assigns to all cells in "Configuration" (dynComments) the
comments of cells in range dynComments by change of sheet
"ActionItemList" - in your posting Sheet2.

How should I change your code?
Thanks Michael

I am confused.......easily done<g

"Configuration" sheet contains text in cells in a range
"dynComments" of row 8

You want the text from each cell in this range to go into Comments in cells
of row 9 of "ActionItemsList" sheet

Am I close yet?


Gord

On Wed, 4 Feb 2009 11:01:58 -0800 (PST), "Michael.Tarnowski"
wrote:

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

Gord,
thank you for the extention to a dynamic version. It works - but in
the wrong direction ;-(
I assume I mixed something up so you did not get the point.
To clarify my intention:

Sheet "Configuration" helds in A8:AF8 the comment text - since this
row may expands I defined a dynamic range "dynComments" for this
In sheet "ActionItemList" the cells in row a9:AF9 shall get the
comment of each cell in dynComments.

Your code assigns to all cells in "Configuration" (dynComments) the
comments of cells in range dynComments by change of sheet
"ActionItemList" - in your posting Sheet2.

How should I change your code?
Thanks Michael

On Feb 2, 6:14 pm, Gord Dibben <gorddibbATshawDOTca wrote:
A little late getting back to you.

Try this revision which updates the Comments' contents.

Sub Comment_Add()
Dim addtext As String
Dim cmt As Comment
Dim rng As Range
Set rng = Sheets("Sheet2").Range("A8:M8")
For Each cell In rng
Set cmt = cell.Comment
addtext = cell.Offset(1, 0).Text
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Text Text:=addtext
Else
cmt.Text Text:=addtext
End If
Next cell
End Sub

To be truly dynamic, you could call it from some type of worksheet event
like calculate.

Private Sub Worksheet_Calculate()
Comment_Add
End Sub

The event code would be inserted into the Sheet2 module and would trigger
when you made a change in any of the row 9 source cells in Sheet1.

Gord

On Sun, 1 Feb 2009 06:26:01 -0800 (PST), "Michael.Tarnowski"
wrote:

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

Gord,
thank you for the extention to a dynamic version. It works - but in
the wrong direction ;-(
I assume I mixed something up so you did not get the point.
To clarify my intention:

Sheet "Configuration" helds in A8:AF8 the comment text - since this
row may expands I defined a dynamic range "dynComments" for this
In sheet "ActionItemList" the cells in row a9:AF9 shall get the
comment of each cell in dynComments.

Your code assigns to all cells in "Configuration" (dynComments) the
comments of cells in range dynComments by change of sheet
"ActionItemList" - in your posting Sheet2.

How should I change your code?
Thanks Michael

On Feb 2, 6:14 pm, Gord Dibben <gorddibbATshawDOTca wrote:
A little late getting back to you.

Try this revision which updates the Comments' contents.

Sub Comment_Add()
Dim addtext As String
Dim cmt As Comment
Dim rng As Range
Set rng = Sheets("Sheet2").Range("A8:M8")
For Each cell In rng
Set cmt = cell.Comment
addtext = cell.Offset(1, 0).Text
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Text Text:=addtext
Else
cmt.Text Text:=addtext
End If
Next cell
End Sub

To be truly dynamic, you could call it from some type of worksheet event
like calculate.

Private Sub Worksheet_Calculate()
Comment_Add
End Sub

The event code would be inserted into the Sheet2 module and would trigger
when you made a change in any of the row 9 source cells in Sheet1.

Gord

On Sun, 1 Feb 2009 06:26:01 -0800 (PST), "Michael.Tarnowski"
wrote:

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

Gord,
thank you for the extention to a dynamic version. It works - but in
the wrong direction ;-(
I assume I mixed something up so you did not get the point.
To clarify my intention:

Sheet "Configuration" helds in A8:AF8 the comment text - since this
row may expands I defined a dynamic range "dynComments" for this
In sheet "ActionItemList" the cells in row a9:AF9 shall get the
comment of each cell in dynComments.

Your code assigns to all cells in "Configuration" (dynComments) the
comments of cells in range dynComments by change of sheet
"ActionItemList" - in your posting Sheet2.

How should I change your code?
Thanks Michael

On Feb 2, 6:14 pm, Gord Dibben <gorddibbATshawDOTca wrote:
A little late getting back to you.

Try this revision which updates the Comments' contents.

Sub Comment_Add()
Dim addtext As String
Dim cmt As Comment
Dim rng As Range
Set rng = Sheets("Sheet2").Range("A8:M8")
For Each cell In rng
Set cmt = cell.Comment
addtext = cell.Offset(1, 0).Text
If cmt Is Nothing Then
Set cmt = cell.AddComment
cmt.Text Text:=addtext
Else
cmt.Text Text:=addtext
End If
Next cell
End Sub

To be truly dynamic, you could call it from some type of worksheet event
like calculate.

Private Sub Worksheet_Calculate()
Comment_Add
End Sub

The event code would be inserted into the Sheet2 module and would trigger
when you made a change in any of the row 9 source cells in Sheet1.

Gord

On Sun, 1 Feb 2009 06:26:01 -0800 (PST), "Michael.Tarnowski"
wrote:

On Jan 31, 12:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
Assuming cells in row 9 of Sheet B are linked to Sheet A

This macro will place whatever is in Sheet B A9:M9 into Comments in A8:M8

Is that what you want?

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Sheets("Sheet2").Range("A8:M8") 'adjust to suit
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(1, 0).Text
End If
Next r
End Sub

Comments will not update if values are changed.

Do you need something dynamic?

Gord Dibben MS Excel MVP

On Fri, 30 Jan 2009 10:48:53 -0800 (PST), "Michael.Tarnowski"

wrote:
Hi community,
I have an Excel application where I append a new line (by a VBA macro
assign to a button) from a kind of template.
Sheet A is the application, sheet B contains the "row template":

Sheet B:
cells in row 08: each cells is a comment text
09: each cell is a header description
10: each cell contains a formula

Row 09 in sheet A is the table's heading defined in sheet B, 09. The
application uses row 03 of Sheet B to append a new line in sheet A.
What I want to achieve is that all comments (sheet B, row 8) are Excel
real comments of the cells of sheet A, row 9.
Any ideas?

Gord you got it - thats exactly what I'am looking for. Dynamic updates
would be great!
Thanks Michael

Gord,
thank you for the extention to a dynamic version. It works - but in
the wrong direction ;-(
I assume I mixed something up so you did not get the point.
To clarify my intention:

Sheet "Configuration" helds in A8:AF8 the comment text - since this
row may expands I defined a dynamic range "dynComments" for this
In sheet "ActionItemList" the cells in row a9:AF9 shall get the
comment of each cell in dynComments.

Your code assigns to all cells in "Configuration" (dynComments) the
comments of cells in range dynComments by change of sheet
"ActionItemList" - in your posting Sheet2.

How should I change your code?
Thanks Michael