Excel: Add NOTES to a range of cells

If you have 2 columns, the first column is your data, and the second column are your comments, you can use the following macro to add the notes as tool tips to the first column. That way you can hover the mouse and see the comments directly instead of scrolling around.

Alternatively, say your cell text is too long to be shown. To see this full text as a tooltip, simply choose the same range for the source and tooltips.

Another method is to use the Data Validation option of "Input Message". The macro below prompts a user to choose between inserting a hovering note or a Data Validation input message.

Bonus tip: To hide text if it goes over the edge of the column width, right-click the cell and: 

Format Cells > Alignment > Horizontal: Fill 

Sub AddComments()

' Adds tooltip comments to a range of cells. The comments are sourced from the second range of cells. This appears on mouse hover.

' Resize the comment to something sensible.

' Alternatively add a tooltip based on Data Validation. This only appears when clicking on a cell.

' Edward Chan 2023

On Error Resume Next

Dim CellsToMod As Range

Set CellsToMod = Application.InputBox("Which cells do you want to add tooltips to?", "Get Range", Application.Selection.Address, Type:=8)

If CellsToMod Is Nothing Then Exit Sub

Dim ToolTips As Range

Set ToolTips = Application.InputBox("Where are the tooltips?", "Get Range", Application.Selection.Address, Type:=8)

If ToolTips Is Nothing Then Exit Sub

On Error GoTo 0

Dim TypeOfToolTip As VbMsgBoxResult

TypeOfToolTip = MsgBox("What type of tooltip? " & Chr(10) & "Choose 'Yes' for a Note on mouse hover." & Chr(10) & "Choose 'No' for a Data Validation input prompt (limited to 255 chars).", vbYesNo)

If TypeOfToolTip = vbYes Then

    For i = 1 To CellsToMod.Count

      With CellsToMod.Cells(i)

        If (Trim(ToolTips.Cells(i).text) = "") Then

          .ClearComments

          ' Do nothing if the value is blank

        Else

          .ClearComments

          .AddComment (ToolTips.Cells(i).text)

        End If

      End With

    Next i

    Comments_AutoSize

End If

If TypeOfToolTip = vbNo Then

    For i = 1 To CellsToMod.Cells.Count

      With CellsToMod.Cells(i).Validation

        If (ToolTips.Cells(i).text = "") Then

          ' Clear the tool tip if blank

          .InputMessage = ""

          .ShowInput = False

        Else

            .Delete

            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _

            :=xlBetween

            .IgnoreBlank = True

            .InCellDropdown = True

            .InputTitle = ""

            .ErrorTitle = ""

            .InputMessage = ToolTips.Cells(i).text

            .ErrorMessage = ""

            .ShowInput = True

            .ShowError = True

        End If

      End With

    Next i

End If

End Sub

Sometimes the comments disappear and you need to reset their location. Run this macro to fix it:

Sub ResetComments()

'Update 20141110

Dim pComment As Comment

For Each pComment In Application.ActiveSheet.Comments

   pComment.Shape.Top = pComment.Parent.Top + 5

   pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5

Next

End Sub

Todo: 

Work with non-contiguous cells using collections

Sub PrintCells()

    Dim myRange As Range

    Dim myCell As Range

    Dim myCollection As New Collection

    On Error Resume Next

    Set myRange = Application.InputBox("Please select a range of cells", Type:=8)

    on error goto 0

    If Not myRange Is Nothing Then

        For Each myCell In myRange.Cells

            myCollection.Add myCell.Value

        Next myCell

        For Each item In myCollection

            Debug.Print item

        Next item

    End If

End Sub