Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move comment into cell as text
I have a spreadsheet that someone else created and they added comments to a
bunch of cells in one column. I want to convert the comments to text in either the same cell or one to the right. (the comments are in blank cells) It's driving me crazy to have to either hover over or click on the comment mark every time I want to see the notes. I also want to import the spreadsheet into a Business Manager Database. When I tried to import it the way it is now the comments did not import. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move comment into cell as text
Hi,
Right click your sheet tab, view code and paste this in and run it. It takes Column A (Change to suit) and puts the comment as text in the same cell. Not the 2 commented out lines. The first is the syntax to copy to an adjacent cell and the second deltes the comment if you want to do that. Sub sonic() Set myrange = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeCo mments) For Each c In myrange c.Value = c.Comment.Text 'c.Offset(0, 1).Value = c.Comment.Text 'c.ClearComments Next End Sub Mike "PhooPhan" wrote: I have a spreadsheet that someone else created and they added comments to a bunch of cells in one column. I want to convert the comments to text in either the same cell or one to the right. (the comments are in blank cells) It's driving me crazy to have to either hover over or click on the comment mark every time I want to see the notes. I also want to import the spreadsheet into a Business Manager Database. When I tried to import it the way it is now the comments did not import. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move comment into cell as text
This macro will put the comments in the same cell it is attached to and then
delete the comments... Sub MoveCommentsToCells() Const WorksheetName As String = "Sheet2" Const CommentColumn As Long = 4 Dim C As Comment For Each C In Worksheets(WorksheetName).Comments With C.Parent If .Column = CommentColumn Then If UBound(Split(.Comment.Text, vbLf)) 0 Then .WrapText = True .Value = C.Text C.Delete End If End With Next End Sub There are two things you have to change in the above so that they apply to your worksheet (and not my test worksheet). Change the Const assignment for the WorksheetName to the name you have on the tab for the worksheet with your comments on them. Also change the Const assignment for the CommentColumn from the test column number of 4 that I used in my test to whatever the column number is with your comment in it. If you are unfamiliar with macros, simply right click the worksheet tab your comments are on, select View Code from the popup menu that appears and copy/paste the above macro into the code window that appeared. You can then make the two changes I just told you about. Finally, place the cursor anywhere inside the code you just pasted and press F5. Go back to your worksheet... the comments should now be in the cells (by the way, if your comments occupied more than one line, then text wrapping for the cell was turned on). -- Rick (MVP - Excel) "PhooPhan" wrote in message ... I have a spreadsheet that someone else created and they added comments to a bunch of cells in one column. I want to convert the comments to text in either the same cell or one to the right. (the comments are in blank cells) It's driving me crazy to have to either hover over or click on the comment mark every time I want to see the notes. I also want to import the spreadsheet into a Business Manager Database. When I tried to import it the way it is now the comments did not import. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move comment into cell as text
Hi Rick,
Unfortunately it didn't work. I double and triple checked your instructions and no go. Not sure if this matters but I'm running Vista Home Premium and MS Excel 2007. Thanks. "Rick Rothstein" wrote: This macro will put the comments in the same cell it is attached to and then delete the comments... Sub MoveCommentsToCells() Const WorksheetName As String = "Sheet2" Const CommentColumn As Long = 4 Dim C As Comment For Each C In Worksheets(WorksheetName).Comments With C.Parent If .Column = CommentColumn Then If UBound(Split(.Comment.Text, vbLf)) 0 Then .WrapText = True .Value = C.Text C.Delete End If End With Next End Sub There are two things you have to change in the above so that they apply to your worksheet (and not my test worksheet). Change the Const assignment for the WorksheetName to the name you have on the tab for the worksheet with your comments on them. Also change the Const assignment for the CommentColumn from the test column number of 4 that I used in my test to whatever the column number is with your comment in it. If you are unfamiliar with macros, simply right click the worksheet tab your comments are on, select View Code from the popup menu that appears and copy/paste the above macro into the code window that appeared. You can then make the two changes I just told you about. Finally, place the cursor anywhere inside the code you just pasted and press F5. Go back to your worksheet... the comments should now be in the cells (by the way, if your comments occupied more than one line, then text wrapping for the cell was turned on). -- Rick (MVP - Excel) "PhooPhan" wrote in message ... I have a spreadsheet that someone else created and they added comments to a bunch of cells in one column. I want to convert the comments to text in either the same cell or one to the right. (the comments are in blank cells) It's driving me crazy to have to either hover over or click on the comment mark every time I want to see the notes. I also want to import the spreadsheet into a Business Manager Database. When I tried to import it the way it is now the comments did not import. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move comment into cell as text
Rick,
Never mind. I got it to work. There was a hidden column so I entered the wrong value. Thank you for this! "Rick Rothstein" wrote: This macro will put the comments in the same cell it is attached to and then delete the comments... Sub MoveCommentsToCells() Const WorksheetName As String = "Sheet2" Const CommentColumn As Long = 4 Dim C As Comment For Each C In Worksheets(WorksheetName).Comments With C.Parent If .Column = CommentColumn Then If UBound(Split(.Comment.Text, vbLf)) 0 Then .WrapText = True .Value = C.Text C.Delete End If End With Next End Sub There are two things you have to change in the above so that they apply to your worksheet (and not my test worksheet). Change the Const assignment for the WorksheetName to the name you have on the tab for the worksheet with your comments on them. Also change the Const assignment for the CommentColumn from the test column number of 4 that I used in my test to whatever the column number is with your comment in it. If you are unfamiliar with macros, simply right click the worksheet tab your comments are on, select View Code from the popup menu that appears and copy/paste the above macro into the code window that appeared. You can then make the two changes I just told you about. Finally, place the cursor anywhere inside the code you just pasted and press F5. Go back to your worksheet... the comments should now be in the cells (by the way, if your comments occupied more than one line, then text wrapping for the cell was turned on). -- Rick (MVP - Excel) "PhooPhan" wrote in message ... I have a spreadsheet that someone else created and they added comments to a bunch of cells in one column. I want to convert the comments to text in either the same cell or one to the right. (the comments are in blank cells) It's driving me crazy to have to either hover over or click on the comment mark every time I want to see the notes. I also want to import the spreadsheet into a Business Manager Database. When I tried to import it the way it is now the comments did not import. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I link the text in a cell into a comment box? | Excel Discussion (Misc queries) | |||
Create Cell Comment based on text in a cell on another worksheet | Excel Discussion (Misc queries) | |||
How do I move comment indicator to different corner of cell? | Excel Worksheet Functions | |||
How do I copy text from a cell into a comment? | Excel Discussion (Misc queries) | |||
How can I move the comment box to the left of the cell? | Excel Worksheet Functions |