Posts Tagged ‘excel cell has comment’

Excel Extract Cell Comments

Posted on the June 19th, 2009 under Cells and Range by Poer @ Excel VBA/Macro

Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here).

Sub CreateCommentsSummary()

    Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer

    ' get all cells with comment
    Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

    ' get cell reference where user want to place the summary
    Set rgOutput = _
        Application.InputBox(Prompt:="Select cell where you want to put the comments summary", _
            Title:="Comments Summary", Type:=8)

    iRow = rgOutput.Row
    iCol = rgOutput.Column

    ' read each cell with comment and build the summary
    For Each rgCell In rgComments
        Cells(iRow, iCol) = rgCell.Address    ' print cell address
        Cells(iRow, iCol + 1) = rgCell.Value    ' print cell value
        Cells(iRow, iCol + 2) = rgCell.Comment.Text    'print cell comment text
        iRow = iRow + 1
    Next rgCell

End Sub

Happy coding :D