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.

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.

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

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

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

Vba category

Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

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

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

Excel categories

Comments (2)