Checking if a cell contains a comment

  • Hi folks


    I wondered if anyone knew how to ask if a cell contains a comment or not.


    If someone knows a solution I would be more than grateful if you could tell me.
    Thanks in advance,
    Regards
    Mike

  • Hi Mike, I dont believe there is a boolean value which will tell you whether a cell as a comment or not. You can have a comment with no text so just seeing if the cell has comment text is not quite enough.


    If you try and obtain the comment text and the cell doesnt have a comment an error occurs. You can use this knowledge to your advantage to create a custom function that will determine if a cell has a comment.


    An alternative to my suggested code below would be to loop through the comments collection in the sheet and check the cell address against your target.


  • Hey Mike,


    I see that Parry got your problem solved while I was taking the boys out for a soda at the convenience store. We (translated - the eight and nine year old) couldn't possibly wait any longer to go do that. Since the wife has the car at her job, we had to :yikes: walk :yikes: . Once outside, it was too tempting to go to the playground, so we played for a while. Anyway, here is what I came up with. True to form with Excel, more than one way to do most things.



    Regards,

  • Another way...


    Code
    Sub Check_for_Comment()
        If Not Intersect(Selection, ActiveSheet.UsedRange.SpecialCells _
                (xlCellTypeComments)) Is Nothing Then MsgBox "Selection has comment(s)."
    End Sub

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Quote from dangelor

    Another way...


    Code
    Sub Check_for_Comment()
        If Not Intersect(Selection, ActiveSheet.UsedRange.SpecialCells _
                (xlCellTypeComments)) Is Nothing Then MsgBox "Selection has comment(s)."
    End Sub


    Nice. :)


    You could even loop through the comments collection if you wished...

  • Quote from parry

    Nice. :)


    You could even loop through the comments collection if you wished...


    Sub YetAnotherWay()


    LMFAO


    Regards,

  • So many ways

    Code
    Set Rng = Range("A1")
    If Rng.Comment Is Nothing Then
        'do stuff
    End If
    Code
    Set Rng = Range("A1")
    If Not Rng.Comment Is Nothing Then
        'do stuff
    End If
  • I dont believe there is a boolean value which will tell you whether a cell as a comment or not. You can have a comment with no text so just seeing if the cell has comment text is not quite enough.

  • royUK

    Closed the thread.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!