CODEWITHSUNDEEP
excelvba
Rick
Rick

Reputation: 9

How to add comment using cell property in VBA

How to add comment using cell property, like i need to add comment to Cells(1, i).

I know how to add comment using Range property (Range("A1").AddComment), but i wanted to add through cells property. Please help in excel vba.

Sub t()
Dim headers() As Variant
Dim i As Integer

headers() = Array("FIRST", "Second", "Third")
For i = 1 To 3
m = Cells(1, i).Value
If Cells(1, i).Value <> headers(i - 1) Then
Cells(1, i).Interior.Color = vbYellow
Cells(1, i).AddComment 'I need to add some text here"

MsgBox ("Not equal")
End If
Next i

End Sub

Upvotes: 0

Views: 8473

Answers (3)

IPG
IPG

Reputation: 1

For future reference, you have to do:

Cells(1, i).address.AddComment

Upvotes: 0

Xiwen Zhang
Xiwen Zhang

Reputation: 11

For whom it may concern, the problem is if you already have a comment, then you have to delete it before you can add more comments. Simply add the following before you addcomment:

**Dim rng As Range
Set rng = Cells(row_num, col_num)
If Not (rng.comment Is Nothing) Then
    rng.comment.Delete
End If**

rng.AddComment (in_comment)

Upvotes: 1

Excelosaurus
Excelosaurus

Reputation: 2849

From the code you've just posted, all you're missing is a string after the AddComment method. Edit: you probably already have comments in some of the target cells. I've added a call to ClearComments.

Sub t()
    Dim headers() As Variant
    Dim i As Integer
    Dim m As Variant

    headers() = Array("FIRST", "Second", "Third")

    For i = 1 To 3
        m = Cells(1, i).Value
        If Cells(1, i).Value <> headers(i - 1) Then
            Cells(1, i).Interior.Color = vbYellow
            Cells(1, i).ClearComments
            Cells(1, i).AddComment "Hello World" '<===== Here
            MsgBox ("Not equal")
        End If
    Next i
End Sub

Upvotes: 2

Related Questions