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