Working with comments – VBA

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. List comments

1.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

1.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

1.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

1.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

1.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

1.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

2. Create comment if cell value is larger than column width

It can sometimes be helpful having a large cell value in a comment. You can then easily hover over cell and read the value instead of resizing column widths.

2.1 How to run macro?

1. Press Alt + F8 to open the Macro dialog box.
   
2. Press with mouse on macro Createcomments to select it.
3. Press with left mouse button on "Run" button to run the selected macro.
4. An input box shows up on your screen asking for a cell range that you want to apply comments to.
   Only cells containing values that don't fit will get a comment.
5. Press with left mouse button on OK button.
6. The macro adds comments.
7. The macro ends.

The animated image below shows what happens when you run macro Createcomments.

2.2 VBA Code

The following subroutine adds a comment to cells where the value exceeds the column width.

'Name macro
Sub Createcomments()

'Dimension variables and declare data types
Dim rng As Range
Dim Cell As Variant

'Enable error handling
On Error Resume Next

'Show inputbox and save input value to object variable rng
Set rng = Application.InputBox(Prompt:="Select a range:", _
Title:="Create comments in cells where the value is larger than column width", _
Default:=Selection.Address, Type:=8)

'Disable error handling
On Error GoTo 0

'Check if variable rang is empty
If rng Is Nothing Then

'Continue if variable rng is not empty
Else

    'Iterate through cells in object rng
    For Each Cell In rng

        'Check if characters in cell value is wider than column width 
        If Len(Cell) * 0.9 > Cell.ColumnWidth Then

            'Check that there is no comment to prevent overwriting older comments
            If Cell.Comment Is Nothing Then
                
                'Add value to cell comment
                Cell.AddComment Cell.Value
            End If
        End If
    Next Cell
End If
End Sub

2.3 Where to put the code?

1. Copy above VBA code.
2. Press Alt+F11 to open the VB Editor.
3. Select your workbook in the Project Explorer.
4. Press with mouse on "Insert" on the menu.
5. Press with mouse on "Module" to insert a code module to your workbook.
6. Paste VBA code to code module.
7. Exit VB Editor and return to Excel.

Get the Excel file

add-comments.xlsm

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
What's on this page Finding the shortest path - A * pathfinding Optimize pick path in a warehouse   1. […]

Resize a range of values (UDF)
The User Defined Function (UDF) demonstrated in this article, resizes a given range to columns or rows you specify. This […]

If then else statement category

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

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 […]

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

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Excel categories

Comments (9)