Adding a comment in a cell based on another cell's value

Question

I want to write a Module or Sub in Excel in order to add a comment into comment section of a cell based on another cell's Value. For example: the value of F2 Cell is "High Level" and by a function like =GetComment(F2) the comment section of B2 cell changed to "High level".

If the value of F2 is empty, "Nothing" should be add into comment section of B2 cell.

The following code does not work, I just tried to show what i mean

Function GetComment(ByVal target As Range, rng As Range)
    If IsEmpty(rng.Value) Then
        target.AddComment ("Nothing")
    Else
        target.AddComment (rng.Value)
    End If
End Function

to better explanation, i captured of excel environment you can see in link below: https://s22.picofile.com/file/8448590268/IMG_20220324_WA0000.jpg

I would be appreciated if anyone help me. Thanks in advance.

Does this answer your question? [Using a UDF in Excel to update the worksheet](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Storax, Apr 08 '22 at 08:16
@Storax - Thank you for your response, but it really did not answer my question. My exact issue is accessing the comment section of a cell and changing it based on another cell's value. — Mohammad Moradi, Apr 08 '22 at 11:35
According to the picture in the post you placed the function `GetComment` into cell B2 and I assume your expectation is that based on the value in F2 the comment in B2 will change accordingly. So, in other words you use a UDF to do that, right? — Storax, Apr 08 '22 at 12:54
@Storax - yes, my expectation is same what you said, I changed the comment of cell B2 manually to better show the my purpose. actually the Function in cell B2 does not work. — Mohammad Moradi, Apr 08 '22 at 13:16

Storax · Accepted Answer · 2022-04-08T13:11:42.600

If you do not use a UDF then you need to change your code like that

Option Explicit

Function GetComment(ByVal target As Range, rng As Range)
    If IsEmpty(rng.Value) Then
        myCmt target, "Nothing"
        'target.AddComment ("Nothing")   <= This code will fail if a comment already exists
    Else
        myCmt target, rng.Value
        'target.AddComment (rng.Value)   <= This code will fail if a comment already exists
    End If
End Function

Function myCmt(rg As Range, cmt As String)
' This will add  the string cmt as a comment to the target
' and will delete any existing comment regardless
    
    ' Just delete any existing comment
    If Not rg.Comment Is Nothing Then
        rg.Comment.Delete
    End If
    rg.AddComment (cmt)

End Function


Sub TestIt()
    GetComment [B2], [F2]
End Sub

In this post you find a workaround how to use an UDF in such a case.

Adding a comment in a cell based on another cell's value

1 Answers1