Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
Is there an easy way within VBA to determine whether a give cell on a
worksheet has a comment associated with it or not? Alan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
If Not ACtivecell.Comment Is Nothing Then
MsgBox "has coment" .... -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan" wrote in message ps.com... Is there an easy way within VBA to determine whether a give cell on a worksheet has a comment associated with it or not? Alan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
Hi
What most people do is On error resume next myCell.AddComment On Error Goto 0 If there is no comment on the cell then one is added, and if one exists then nothing happens. Now simply carry on with whatever you want to do with the comment. If you really need to know that a comment exists or not try Public Function DoesCommentExist(myCell As Range) As Boolean Dim myComment As Shape DoesCommentExist = False Err.Clear On Error Resume Next Set myComment = myCell.Comment.Shape If Err.Number = 0 Then DoesCommentExist = True End Function Run this macro on the activecell with and without a comment. Sub tester() MsgBox DoesCommentExist(ActiveCell) End Sub Interestingly, Set myComment = myCell.Comment does not work and always returns true. regards Paul On Oct 24, 11:31 am, Alan wrote: Is there an easy way within VBA to determine whether a give cell on a worksheet has a comment associated with it or not? Alan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
On 24 Oct, 11:40, "Bob Phillips" wrote:
If Not ACtivecell.Comment Is Nothing Then MsgBox "has coment" ... -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan" wrote in message ps.com... Is there an easy way within VBA to determine whether a give cell on a worksheet has a comment associated with it or not? Alan- Hide quoted text - - Show quoted text - Thanks Bob, that was just what I needed. I was using "On Error" as Paul was suggesting but I have so many cases to consider my "GoTos" were getting a bit complicated. Alan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
wrote in message oups.com... Interestingly, Set myComment = myCell.Comment does not work and always returns true. What do you mean Paul? If myComment is a Comment object, that code will either return Nothing or the comment object. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comment or not
On Oct 24, 3:03 pm, "Bob Phillips" wrote:
wrote in message oups.com... Interestingly, Set myComment = myCell.Comment does not work and always returns true. What do you mean Paul? If myComment is a Comment object, that code will either return Nothing or the comment object. Hi Bob, Just me not thinking it through. I thought Set myComment = myCell.AddComment would raise an error if myCell had no comment, but it doesn't of course, it just returns Nothing. The code was inside an On Error... block so I couldn't see the none existent error! regards Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
How do I remove the red corner in excel comment and keep comment? | Excel Programming | |||
How can I edit a comment w/o first having to select Show Comment | Excel Discussion (Misc queries) | |||
How do I insert a comment when the Insert:Comment menu is greyed? | Excel Discussion (Misc queries) | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions |