Add comments to cells using VBA

Question

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.

Answer 1

hovering over any cell, that contains a comment, shows that cell's comment

this is how you add a comment to a cell and how you update the comment text

Sub aaa()
    With Range("E6")
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment "this is a comment"
        .Comment.Text "abc123" 'No need the assignment sign "=" after .Comment.Text             
    End With
End Sub

Answer 2

Try this code.

Sub test()
    Dim rngDB As Range, rngComent As Range
    Dim rng As Range
    Dim cm As Comment, i as integer
    Set rngComent = Sheets(1).Range("a1:a5")
    Set rngDB = Sheets(2).Range("b1:b5")

    For Each rng In rngComent
        i = i + 1
        If Not rng.Comment Is Nothing Then
            rng.Comment.Delete
        End If
        Set cm = rng.AddComment
        With cm
            .Visible = False
            .Text Text:=rngDB(i).Value
        End With
    Next rng

End Sub