How to create a list of comments from a worksheet programmatically

Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left mouse button on "Special..." button, select "Comments" and then press with left mouse button on OK.

1. How to record a macro?

What happens if we record a macro while performing these actions? Here is how you do that:

1. Go to tab "Developer" on the ribbon. How to enable the Developer tab on the ribbon
   
2. Press with left mouse button on the "Record macro" button. A dialog box appears.
   
3. Type a name if you don't want to use the default value.
4. Press with left mouse button on OK button.
5. Perform the actions you want to record.
6. Press with left mouse button on "Stop recording" when finished.

This is what the macro recorder returns:

Sub Macro1()
Selection.SpecialCells(xlCellTypeComments).Select
End Sub

We can use this VBA code to retrieve all comments, their cell values, addresses, and so on.

Microsoft Docs: SpecialCells method

Back to top

2. How to create a list of all comments in a worksheet?

The following VBA code iterates through each cell containing a comment and saves the cell value, comment and address to a new worksheet.

'Name macro
Sub Macro1()

'Dimension variables and declare data types
Dim WS As Worksheet
Dim Rng As Range
Dim cell As Variant
Dim i As Single

'The SET statement allows you to save an object reference to a variable
'Select all cells with comments
Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

'Insert a new workshhet and save it to object WS
Set WS = Sheets.Add

'Save "Address" to cell A1 in worksheet WS
WS.Range("A1") = "Address"

'Save "Comment" to cell B1 in worksheet WS
WS.Range("B1") = "Comment"

'Save "Cell value" to cell C1 in worksheet WS
WS.Range("C1") = "Cell value"

'Save 2 to variable i
i = 2

'Iterate through all selected cells in Rng
For Each cell In Rng

    'Save cell address to a cell in column A
    WS.Range("A" & i) = cell.Address

    'Save cell comment to a cell in column B
    WS.Range("B" & i) = cell.Comment.Text

    'Save cell address to a cell in column A
    WS.Range("C" & i) = cell.Value

    'Add 1 to variable i
    i = i + 1

'Continue with next cell
Next cell
End Sub

Back to top

3. How to build a list of comments from all worksheets in a workbook?

This VBA code checks all worksheets in the current workbook for comments and saves them to a new worksheet.

'Name macro
Sub Macro2()

'Dimension variables and declare data types
Dim WS As Worksheet, WoSh As Worksheet
Dim Rng As Range
Dim cell As Variant
Dim i As Single

'The SET statement allows you to save an object reference to a variable
'Insert a new workshhet and save it to object WS
Set WS = Sheets.Add

'Save "Sheet" to cell A1 in worksheet WS
WS.Range("A1") = "Sheet"

'Save "Address" to cell B1 in worksheet WS
WS.Range("B1") = "Address"
'Save "Comment" to cell C1 in worksheet WS
WS.Range("C1") = "Comment"

'Save "Cell value" to cell D1 in worksheet WS
WS.Range("D1") = "Cell value"

'Save 2 to variable i
i = 2

'Iterate through all worksheets in Active workbook
For Each WoSh In ActiveWorkbook.Worksheets

    'Enable error handling
    On Error Resume Next

    'The SET statement allows you to save an object reference to a variable
    'Select all cells with comments
    Set Rng = WoSh.Cells.SpecialCells(xlCellTypeComments)

    'Check if range is not empty
    If Not Rng Is Nothing Then

        'Iterate through all selected cells in Rng
        For Each cell In Rng

            'Save worksheet name to cell in column A
            WS.Range("A" & i) = WoSh.Name

            'Save cell address to cell in column B
            WS.Range("B" & i) = cell.Address

            'Save cell comment to cell in column C
            WS.Range("C" & i) = cell.Comment.Text

            'Disable wrap text
            WS.Range("C" & i).WrapText = False

            'Save cell value to cell in column D
            WS.Range("D" & i) = cell.Value

            'Add 1 to variable i
            i = i + 1

        'Continue with next cell
        Next cell
    End If

    'Clear object Rng
    Set Rng = Nothing
Next WoSh

End Sub

Back to top

4. How to build a list of comments from all worksheets in all open workbooks?

This VBA code checks all open workbooks for comments and saves them to a new worksheet.

'Name macro
Sub Macro3()

'Dimension variables and declare data types
Dim WS As Worksheet, WoSh As Worksheet
Dim WB As Workbook
Dim Rng As Range
Dim cell As Variant
Dim i As Single

'Insert a new worksheet and save to object WS
Set WS = Sheets.Add

'Save "Workbook" to cell A1 in worksheet WS
WS.Range("A1") = "Workbook"

'Save "Sheet" to cell B1 in worksheet WS
WS.Range("B1") = "Sheet"

'Save "Address" to cell C1 in worksheet WS
WS.Range("C1") = "Address"

'Save "Comment" to cell D1 in worksheet WS
WS.Range("D1") = "Comment"

'Save "Cell value" to cell E1 in worksheet WS
WS.Range("E1") = "Cell value"

'Save 2 to variable i
i = 2

'Iterate through all open workbooks using variable WB
For Each WB In Workbooks

    'Iterate through all worksheets in workbook WB
    For Each WoSh In WB.Worksheets

        'Enable error handling
        On Error Resume Next

        'Select all cells with comments
        Set Rng = WoSh.Cells.SpecialCells(xlCellTypeComments)

        'Check if RNG is not empty
        If Not Rng Is Nothing Then

            'Iterate through all selected cells
            For Each cell In Rng

                'Save workbook name to a cell in column A
                WS.Range("A" & i) = WB.Name

                'Save worksheet name to a cell in column B
                WS.Range("B" & i) = WoSh.Name

                'Save cell address to a cell in column C
                WS.Range("C" & i) = cell.Address

                'Save cell address to a cell in column D
                WS.Range("D" & i) = cell.Comment.Text

                'Disable wrap text
                WS.Range("D" & i).WrapText = False

                'Save cell value to a cell in column E
                WS.Range("E" & i) = cell.Value

                'Add 1 to variable i
                i = i + 1

            'Continue witrh next cell
            Next cell
        End If

        'Clear object Rng
        Set Rng = Nothing

    'Continue with next worksheet
    Next WoSh

'Continue with next workbook
Next WB

'Resize column widths to text
Columns("A:E").AutoFit
End Sub

Back to top

5. How to build a list of comments from all workbooks in a folder?

I have reused some code from this macro: Search all workbooks in a folder

'Name macro
Sub Macro4()

'Dimension variables and declare data types
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim c As Range

'Insert a new worksheet
Set WS = Sheets.Add

'Show dialog box and ask for a Folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

'Save "Workbook" to cell A1 in worksheet WS 
WS.Range("A1") = "Workbook" 

'Save "Sheet" to cell B1 in worksheet WS 
WS.Range("B1") = "Sheet" 

'Save "Address" to cell C1 in worksheet WS 
WS.Range("C1") = "Address" 

'Save "Comment" to cell D1 in worksheet WS 
WS.Range("D1") = "Comment" 

'Save "Cell value" to cell E1 in worksheet WS 
WS.Range("E1") = "Cell value"

'Save 0 (zero) to variable i
a = 0

'Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Value = Dir(myfolder)

'Keep iterating until folder is empty
Do Until Value = ""

    'Check that file name is not . or ..
    If Value = "." Or Value = ".." Then
    Else

        'Check that file name is an Excel file with fiel extensions xls, xlsx or xlsm
        If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then

            'Enable error handling
            On Error Resume Next

            'Check if workbook is password protected
            Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"

            'Check if an error has occured (meaning the workbook is password protected)
            If Err.Number > 0 Then

                'Save to cell in column A workbook password protected
                WS.Range("A2").Offset(a, 0).Value = Value & " : Password protected"

                'Add 1 to variable i
                a = a + 1

            'Continue here if no error has happened
            Else
                
                'Disable error handling
                On Error GoTo 0

                'Iterate through all worksheets in workbook
                For Each sht In ActiveWorkbook.Worksheets

                        ' Enable error handling
                        On Error Resume Next

                        'Select all cells with comments and save to object c
                        Set c = sht.Cells.SpecialCells(xlCellTypeComments)

                        'Check if c is not empty
                        If Not c Is Nothing Then

                            'Iterate through each cell in c
                            For Each cell In c

                                'Save folder path to cell in column A
                                WS.Range("A2").Offset(a, 0).Value = Value

                                'Save worksheet name to a cell in column B
                                WS.Range("B2").Offset(a, 0).Value = sht.Name

                                'Save cell address to cell in column C
                                WS.Range("C2").Offset(a, 0).Value = cell.Address

                                'Save cell comment to a cell in column D
                                WS.Range("D2").Offset(a, 0).Value = cell.Comment.Text

                                'Disable wrap text
                                WS.Range("D2").Offset(a, 0).WrapText = False

                                'Add 1 to variable a
                                a = a + 1

                            'Continue with next cell
                            Next cell
                        End If

                        'Clear object c
                        Set c = Nothing

                'Continue with next worksheet
                Next sht
            End If

            'Close active workbook
            Workbooks(Value).Close False

            'Disable error handling
            On Error GoTo 0
        End If
    End If
    Value = Dir

'Continue witrh next file
Loop

'Resize column widths
Cells.EntireColumn.AutoFit
End Sub

Back to top

6. Where to put the VBA code?

1. Press Alt + F11 to open the Visual Basic Editor (VBE).
2. Press with left mouse button on "Insert" on the top menu.
3. Press with left mouse button on "Module" to insert a new module.
4. Copy VBA code.
5. Paste to code window.
6. Return to Excel.

Interesting articles you must read

List all open workbooks and corresponding sheets (vba)

Extract cell references from all cell ranges populated with values in a sheet

Get the Excel file

List-comments1.xlsm

Back to top

For next statement category

Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet.  You will then be able to quickly […]

Finding the shortest path – A * pathfinding
Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]

Combine cell ranges ignore blank cells
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

If then else statement category

Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Excel categories

Comments (7)