Author: Oscar Cronquist Article last updated on August 26, 2019

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

    '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.
Note, save your workbook with file extension *.xlsm in order to keep the code to your workbook. If not, the code will be gone the next time you open up the same workbook.

Get the Excel file