convert cell contents into a comment

[Paul hunter]

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.

 

[William]

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

(e-mail address removed)

| 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.
|
|

 

[Paul Hunter]

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.

 

[Paul Hunter]

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

 

[Paul Hunter]

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!

 

[William]

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

(e-mail address removed)

| 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
| >>>
| >>>[email protected]
| >>>
| >>>"Paul hunter" <[email protected]>
| >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.
| >>>|
| >>>|
| >>>
| >>>
| >>>.
| >>>
| >>.
| >>
| >.
| >