Hi

Is there a quick way to convert the cell contents into a
comment?

I have several cells with varying lengths of text in them.
I want to clean the sheet up by putting all those cells
contents into comments.

I'm doing this manually and, boy, is this tedious!

Thanks for any help.

Hi Paul

Firstly, select all the cells that you want to add a comment to, and then
name that range "cmts". The try the following.

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.ClearComments
c.AddComment
c.Comment.Text Text:=c.Text
'Add this line to clear the text from the cell
c.Clear
Next c
End With
End Sub


--
XL2002
Regards

William



"Paul hunter" wrote in message
...
| Hi
|
| Is there a quick way to convert the cell contents into a
| comment?
|
| I have several cells with varying lengths of text in them.
| I want to clean the sheet up by putting all those cells
| contents into comments.
|
| I'm doing this manually and, boy, is this tedious!
|
| Thanks for any help.
|
|

Thanks for the reply, William. Unfortunately, that didn't
work. All it did was clear the contents on the cells. No
problem though, as I just closed the workbook without
saving.

Maybe a different approach is needed. I tried using the
macro recorder to do this. It will work if I can find a
way to not have to define the actual cell in the macro. I
don't know how to do that.

Here are the steps I performed and the code that was
generated.

1. copy the cell contents to Notepad.
2. re-select the cell
3. insert comment
4. paste the contents of the cell into the comment box.

Here is the code:

Sub Macro1()

Range("L5").Select
Selection.Copy
Application.CutCopyMode = False
Range("L5").AddComment
Range("L5").Comment.Visible = False
Range("L5").Comment.Text Text:=" :" & Chr(10)
& "V/MC total entered incorrectly" & Chr(10) & ""
Range("L5").Select
End Sub

If I could change the first line of the code to not be a
specific cell selction instead it would be whatever cell I
select and if the line that defines the comment text could
just refer to the selected cell itself, I could live with
doing this one cell at a time.

For example, I select cell A11 and run the macro on that
cell and the comment text would be the contents of A11.

Thanks for any further help.

-----Original Message-----
Hi Paul

Firstly, select all the cells that you want to add a
comment to, and then
name that range "cmts". The try the following.

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.ClearComments
c.AddComment
c.Comment.Text Text:=c.Text
'Add this line to clear the text from the cell
c.Clear
Next c
End With
End Sub


--
XL2002
Regards

William



"Paul hunter" wrote
in message
...
| Hi
|
| Is there a quick way to convert the cell contents into a
| comment?
|
| I have several cells with varying lengths of text in
them.
| I want to clean the sheet up by putting all those cells
| contents into comments.
|
| I'm doing this manually and, boy, is this tedious!
|
| Thanks for any help.
|
|


.

I got the macro to work by removing this line:

c.ClearComments

Progress!

Now, I found this piece of code by in the Google archives:

Public Sub Comment_Size()
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Sub

How can I incorporate that into this:

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.AddComment
c.Comment.Text Text:=c.Text
Next c
End With
End Sub

Thanks for any assistance

-----Original Message-----
Thanks for the reply, William. Unfortunately, that didn't
work. All it did was clear the contents on the cells. No
problem though, as I just closed the workbook without
saving.

Maybe a different approach is needed. I tried using the
macro recorder to do this. It will work if I can find a
way to not have to define the actual cell in the macro. I
don't know how to do that.

Here are the steps I performed and the code that was
generated.

1. copy the cell contents to Notepad.
2. re-select the cell
3. insert comment
4. paste the contents of the cell into the comment box.

Here is the code:

Sub Macro1()

Range("L5").Select
Selection.Copy
Application.CutCopyMode = False
Range("L5").AddComment
Range("L5").Comment.Visible = False
Range("L5").Comment.Text Text:=" :" & Chr(10)
& "V/MC total entered incorrectly" & Chr(10) & ""
Range("L5").Select
End Sub

If I could change the first line of the code to not be a
specific cell selction instead it would be whatever cell
I
select and if the line that defines the comment text
could
just refer to the selected cell itself, I could live with
doing this one cell at a time.

For example, I select cell A11 and run the macro on that
cell and the comment text would be the contents of A11.

Thanks for any further help.

-----Original Message-----
Hi Paul

Firstly, select all the cells that you want to add a
comment to, and then
name that range "cmts". The try the following.

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.ClearComments
c.AddComment
c.Comment.Text Text:=c.Text
'Add this line to clear the text from the cell
c.Clear
Next c
End With
End Sub


--
XL2002
Regards

William



"Paul hunter"
wrote
in message
...
| Hi
|
| Is there a quick way to convert the cell contents into
a
| comment?
|
| I have several cells with varying lengths of text in
them.
| I want to clean the sheet up by putting all those cells
| contents into comments.
|
| I'm doing this manually and, boy, is this tedious!
|
| Thanks for any help.
|
|


.

.

Ok, disregard the last post. I incorporated both pieces of
code but the entire comment is all placed in a single line.

So, how do I auto size a comment box so that the entire
comment, which could be hundreds of characters in length,
is not all in a single line?

Again, I thank anyone for their assistance!

-----Original Message-----
I got the macro to work by removing this line:

c.ClearComments

Progress!

Now, I found this piece of code by in the Google archives:

Public Sub Comment_Size()
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Sub

How can I incorporate that into this:

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.AddComment
c.Comment.Text Text:=c.Text
Next c
End With
End Sub

Thanks for any assistance

-----Original Message-----
Thanks for the reply, William. Unfortunately, that
didn't
work. All it did was clear the contents on the cells. No
problem though, as I just closed the workbook without
saving.

Maybe a different approach is needed. I tried using the
macro recorder to do this. It will work if I can find a
way to not have to define the actual cell in the macro.
I
don't know how to do that.

Here are the steps I performed and the code that was
generated.

1. copy the cell contents to Notepad.
2. re-select the cell
3. insert comment
4. paste the contents of the cell into the comment box.

Here is the code:

Sub Macro1()

Range("L5").Select
Selection.Copy
Application.CutCopyMode = False
Range("L5").AddComment
Range("L5").Comment.Visible = False
Range("L5").Comment.Text Text:=" :" & Chr(10)
& "V/MC total entered incorrectly" & Chr(10) & ""
Range("L5").Select
End Sub

If I could change the first line of the code to not be a
specific cell selction instead it would be whatever cell
I
select and if the line that defines the comment text
could
just refer to the selected cell itself, I could live
with
doing this one cell at a time.

For example, I select cell A11 and run the macro on that
cell and the comment text would be the contents of A11.

Thanks for any further help.

-----Original Message-----
Hi Paul

Firstly, select all the cells that you want to add a
comment to, and then
name that range "cmts". The try the following.

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.ClearComments
c.AddComment
c.Comment.Text Text:=c.Text
'Add this line to clear the text from the cell
c.Clear
Next c
End With
End Sub


--
XL2002
Regards

William



"Paul hunter"
wrote
in message
.. .
| Hi
|
| Is there a quick way to convert the cell contents
into
a
| comment?
|
| I have several cells with varying lengths of text in
them.
| I want to clean the sheet up by putting all those
cells
| contents into comments.
|
| I'm doing this manually and, boy, is this tedious!
|
| Thanks for any help.
|
|


.

.

.

Hi Paul

Try this

Sub test()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.ClearComments
c.AddComment
c.Comment.Text Text:=c.Text
c.Comment.Shape.TextFrame.AutoSize = False
'Add this line to clear the text from the cell
c.ClearContents
Next c
End With
End Sub

--
XL2002
Regards

William



"Paul Hunter" wrote in message
...
| Ok, disregard the last post. I incorporated both pieces of
| code but the entire comment is all placed in a single line.
|
| So, how do I auto size a comment box so that the entire
| comment, which could be hundreds of characters in length,
| is not all in a single line?
|
| Again, I thank anyone for their assistance!
|
| -----Original Message-----
| I got the macro to work by removing this line:
|
| c.ClearComments
|
| Progress!
|
| Now, I found this piece of code by in the Google archives:
|
| Public Sub Comment_Size()
| Dim cmt As Comment
| Dim cmts As Comments
| Set cmts = ActiveSheet.Comments
| For Each cmt In cmts
| cmt.Shape.TextFrame.AutoSize = True
| Next
| End Sub
|
| How can I incorporate that into this:
|
| Sub test()
| With ActiveSheet
| Dim r As Range, c As Range
| Set r = .Range("cmts")
| For Each c In r
| c.AddComment
| c.Comment.Text Text:=c.Text
| Next c
| End With
| End Sub
|
| Thanks for any assistance
|
| -----Original Message-----
| Thanks for the reply, William. Unfortunately, that
| didn't
| work. All it did was clear the contents on the cells. No
| problem though, as I just closed the workbook without
| saving.
|
| Maybe a different approach is needed. I tried using the
| macro recorder to do this. It will work if I can find a
| way to not have to define the actual cell in the macro.
| I
| don't know how to do that.
|
| Here are the steps I performed and the code that was
| generated.
|
| 1. copy the cell contents to Notepad.
| 2. re-select the cell
| 3. insert comment
| 4. paste the contents of the cell into the comment box.
|
| Here is the code:
|
| Sub Macro1()
|
| Range("L5").Select
| Selection.Copy
| Application.CutCopyMode = False
| Range("L5").AddComment
| Range("L5").Comment.Visible = False
| Range("L5").Comment.Text Text:=" :" & Chr(10)
| & "V/MC total entered incorrectly" & Chr(10) & ""
| Range("L5").Select
| End Sub
|
| If I could change the first line of the code to not be a
| specific cell selction instead it would be whatever cell
| I
| select and if the line that defines the comment text
| could
| just refer to the selected cell itself, I could live
| with
| doing this one cell at a time.
|
| For example, I select cell A11 and run the macro on that
| cell and the comment text would be the contents of A11.
|
| Thanks for any further help.
|
| -----Original Message-----
| Hi Paul
|
| Firstly, select all the cells that you want to add a
| comment to, and then
| name that range "cmts". The try the following.
|
| Sub test()
| With ActiveSheet
| Dim r As Range, c As Range
| Set r = .Range("cmts")
| For Each c In r
| c.ClearComments
| c.AddComment
| c.Comment.Text Text:=c.Text
| 'Add this line to clear the text from the cell
| c.Clear
| Next c
| End With
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Paul hunter"
| wrote
| in message
| .. .
| | Hi
| |
| | Is there a quick way to convert the cell contents
| into
| a
| | comment?
| |
| | I have several cells with varying lengths of text in
| them.
| | I want to clean the sheet up by putting all those
| cells
| | contents into comments.
| |
| | I'm doing this manually and, boy, is this tedious!
| |
| | Thanks for any help.
| |
| |
|
|
| .
|
| .
|
| .
|