Thursday, March 8, 2012

How to convert text to Comments using VBA

Sub Convert_Text_To_Comments()

Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name

sUser = Application.UserName

For i1 = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

sText = ActiveSheet.Cells(i1, 5).Value

'Deletes Existing Comments
Cells(i1, 3).ClearComments

' Creates Comment
Cells(i1, 3).AddComment
Cells(i1, 3).Comment.Text Text:=sUser & Chr(10) & sText

Next i1

End Sub

No comments: