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 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
hmm doesn't seem to work, sorry :?
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.
Sub FindComment()
MsgBox HasComment(ActiveCell)
End Sub
Function HasComment(Cell As Range) As Boolean
Dim Tmp
If Cell.Cells.Count > 1 Then Exit Function
On Error Resume Next
Tmp = Cell.Comment.Text
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
HasComment = False
Else
On Error GoTo 0
HasComment = True
End If
End Function
Display More
That code you posted is great Parry.
Thanks for that mate!
Mike
My pleasure. :kiwibird:
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.
Option Explicit
Dim Targetcells As Range
Dim MyCell As Range
Sub FindComments()
Set MyCell = Range(ActiveCell.Address)
Selection.SpecialCells(xlCellTypeComments).Select
Set Targetcells = Selection
If Application.Intersect(MyCell, Targetcells) Is Nothing _
Then MsgBox ("No Comment") _
Else MsgBox ("Comment in " & MyCell.Address)
MyCell.Select
End Sub
Display More
Regards,
Quote from dangelor
Nice.
You could even loop through the comments collection if you wished...
Sub YetAnotherWay()
Dim Cmt As Comment
'loop through all comments in the active sheet
For Each Cmt In ActiveSheet.Comments
'If comments address is same as activecell we have a hit
If Cmt.Parent.Address = ActiveCell.Address Then
MsgBox "ActiveCell contains a comment!", vbInformation, "COMMENT FOUND"
Exit Sub 'no need to look further
End If
Next Cmt
'This will only run if theres no comments because of exit sub above.
MsgBox "ActiveCell does not have a comment.", vbInformation, "NO COMMENT FOUND"
End Sub
Display More
Quote from parryNice.
You could even loop through the comments collection if you wished...
Code Display MoreSub YetAnotherWay() Dim Cmt As Comment 'loop through all comments in the active sheet For Each Cmt In ActiveSheet.Comments 'If comments address is same as activecell we have a hit If Cmt.Parent.Address = ActiveCell.Address Then MsgBox "ActiveCell contains a comment!", vbInformation, "COMMENT FOUND" Exit Sub 'no need to look further End If Next Cmt 'This will only run if theres no comments because of exit sub above. MsgBox "ActiveCell does not have a comment.", vbInformation, "NO COMMENT FOUND" End Sub
Sub YetAnotherWay()
LMFAO
Regards,
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.
18 year old-thread...
Don’t have an account yet? Register yourself now and be a part of our community!