Microsoft Excel - Excel VBA for comments to cells and vice versa

Asked By Gauss on 29-Mar-12 02:32 AM
Hello to all Ms Excel, vba experts,

I have worksheet with scattered comments in cells,
I want a macro that when run goes to all cells with comments and copies texts from comments to respective cells and text that were in cells to comments, the range is from F7:AJ100.

Kindly please help me out, Gauss.
Anoop S replied to Gauss on 29-Mar-12 04:36 AM
Try this code to copy excel comment to clipboard

Sub PutStringInClipboard()
'Sub routine to put a string in the clipboard
 
Dim ans As String
Dim ansDataO As DataObject
Set ansDataO = New DataObject
 
ans = "Some Text"
 
ansDataO.SetText ans
ansDataO.PutInClipboard
    
End Sub
 
 
The following macro will copy comment text to the cell to the right, if that cell is empty.
 
Sub ShowCommentsNextCell()
  Application.ScreenUpdating = False
 
  Dim commrange As Range
  Dim mycell As Range
  Dim curwks As Worksheet
  Dim ans As String
  Dim ansDataO As DataObject
  Set ansDataO = New DataObject
   
  Set curwks = ActiveSheet
 
  On Error Resume Next
  Set commrange = curwks.Cells _
  .SpecialCells(xlCellTypeComments)
  On Error GoTo 0
 
  If commrange Is Nothing Then
   MsgBox "no comments found"
   Exit Sub
  End If
 
  For Each mycell In commrange
   ans= ans+mycell.Comment.Text 'Getting comment in a range
  Next mycell
   
  ansDataO.SetText ans
  ansDataO.PutInClipboard 'pass comment string to clipboard
 
  Application.ScreenUpdating = True
 
End Sub

reference
http://www.contextures.com/xlcomments03.html
Somesh Yadav replied to Gauss on 29-Mar-12 04:57 AM
Hi,
refer this,

http://www.exceltip.com/st/Changing_an_Absolute_Reference_to_a_Relative_Reference_or_Vice_Versa/123.html

Hope it helps you.
Gauss replied to Anoop S on 29-Mar-12 07:59 AM
Thank you,

But I want the comment contents to be in the selected cell, and selected cell content to the comment, the procedure to loop through all cells with comment in the sepcified range.

Thanks, Gauss.
wally eye replied to Gauss on 29-Mar-12 06:52 PM
Entirely possible, if I am reading this right.  You want to swap the cell text for the comments, that is place the comments into the cell and the cell contents into the comment?

Anoop has a good start on it:

Public Sub SwapComments()
  
  Dim commrange    As Excel.Range
  Dim mycell        As Excel.Range
  
  Dim strswap      As String
  
  Set commrange = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
  For Each mycell In commrange.Cells
    strswap = mycell.Value
    mycell.Value = mycell.Comment.Text
    mycell.Comment.Delete
    mycell.AddComment strswap
  Next mycell
  
  Set commrange = Nothing
  
End Sub


Gauss replied to wally eye on 05-Apr-12 03:38 AM
Hello Wall Eye,

Thank you so much for your reply, it worked perfectly as wanted.

Thanks a million. Gauss.
wally eye replied to Gauss on 08-Apr-12 01:28 PM
Glad to hear it, thanks for the feedback!