Add comments to cells using VBA

user_8411961 0
Viewed 21266

Is there a way to activate a comment on a cell by hovering over it? I have a range of cells that I would like to pull respective comments from another sheet when hovered over each individual cell. The hover event would pull the comments from their respective cells in the other sheet.

The comments are of string value. Basically, I have a range of cells in Sheet 1, let's say A1:A5 and I need comments to pop-up when I hover over them and pull from Sheet 2 range B1:B5. The reason why I won't do it manually is because the contents of Sheet 2 change every day. That is why I am trying to see if there is a VBA solution.

excel-2013 vba excel
5 Answers

A less bulky "All-in-One" solution:

Sub comment(rg As Range, Optional txt As String = "")
  If rg.comment Is Nothing Then
    If txt <> "" Then rg.addComment txt
  Else
    If txt = "" Then rg.comment.Delete Else rg.comment.text txt
  End If
End Sub

Usage:

Using cell [a1] as an example ...but shortcut notation like [a1]) should generally be avoided except for testing, etc

  • Add or change comment: comment [a1], "This is my comment!"
  • Delete existing comment: comment [a1], "" or simply comment [a1]

Related stuff:

  • set comment box size:[a1].comment.Shape.Width = 15 and[a1].comment.Shape.Height = 15
  • set the box position: [a1].comment.Shape.Left=10 and [a1].comment.Shape.Top=10
  • change background box color: [a1].comment.Shape.Fill.ForeColor.RGB = vbGreen

Nowadays I think that comments (or "notes", as they're now called) are hidden by default.

  • Always show all comments: Application.DisplayCommentIndicator=1

  • Show when mouse hovers over cell: Application.DisplayCommentIndicator=-1

  • Disable comments (hide red indicator): Application.DisplayCommentIndicator=0

  • show/hide individual comments like [a1].comment.visble=true, etc.

  • get comment text: a=[a1].comment.text

user_81127760

I've discovered that if the sheet cell is previously formatted and contains data the VBA Add Comments routines may not work. Also, you have to refer to the cell in the "Range" ("A1") format, not the "Cells" (Row Number, Column Number) format. The following short sub worked for me (utilize prior to program formatting/adding data to cell):

Sub Mod01AddComment()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim WkSheet As Worksheet
Set WkSheet = wb.Sheets("Sheet1")

Dim CellID As Range

Set CellID = WkSheet.Cells(RowNum, ColNum)
` ( or, Set CellID = WkSheet.Range("A1") )

CellID.Clear

CellID.AddComment
CellID.Comment.Visible = False
CellID.Comment.Text Text:="Comment Text"

End Sub
user_175257930
Related