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.

Solution 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

Solution 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

Comments

[Dirk Reichel]

you can rightclick a cell and add a comment.... but to set the text dynamically will need a bit of vba... please show what you have tried so far

[TJYen]

Are the comments of string value? and are the comments being pulled from cells or comment boxes in the other sheet?

[studentofarkad]

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.

[DMM]

There is no built-in hover event (try seeing what happens when you use the macro recorder to hover over a commented cell - the comment shows in Excel's interface but nothing gets added to the recorded macro). So, you will likely need a macro that looks pairwise at cells in your source and target ranges and sets the Comment property of the target cell to be that of the source cell.

[jsotola]

which Range("E1:E10") is on an undetermined worksheet

[studentofarkad]

Unfortunately, I get "variable" not defined as the error message.

[Dy.Lee]

@studentofarkad, i as integer missed.