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