Search notes:

Office Object Model: Excel - Comment

A comment can be used to add additional notes («comments») to a cell. Its content is displayed in a so-called «balloon».

Methods and properties

`application`
`author`
`creator`
`delete()`
`next()`
`parent`
`previous()`
`shape`
`text()`
`visible`

Insert a comment

option explicit

sub main() ' {

    dim cl as range
    dim cm as comment
    dim sh as shape

    set cl = cells(2,2)
    cl.value = "foo"

    set cm = cl.addComment
  '
  ' Note: the text of the comment is assigned with a method, not
  '       a property:
  '
    cm.text("A comment that" & chr(10) & "should describe foo.")
    cm.visible = true

    set sh = cm.shape

    sh.height = 25
    sh.width  = 90

    activeWorkBook.saved = true

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Comment/insert.bas

Apparently, a shorthand to insert a comment is to use a range's noteText() method.

Showing/hiding a comment with a mouse click

The worksheet_selectionChange() event handler captures changes of the selected cells, for example if the user clicks on (another) cell on the worksheet.

The following example checks whether the changed cell is in a specific range, and if so, hides all visible comments in that range and shows the comment for the selected range.

option explicit

sub worksheet_selectionChange(byVal rng as range) ' {

    if rng.count <> 1 then
       exit sub
    end if

    if rng.row = 5 and rng.column >= 2 and rng.column <= 37 then
       dim cel as range
       for each cel in range(cells(5,2), cells(5,37)) 
           cel.comment.visible = false
       next cel

       rng.comment.visible = true
    end if

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Comment/show-hide-selectionChange.bas

See also the Excel helpers: insert a comment