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

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.

CodePudding user response：

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.