[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Detecting if a Comment is in a Cell

Posted on 2011-05-02
20
Medium Priority
?
293 Views
Last Modified: 2012-05-11
I would like to determine if a Cell already has a comment, prior to performing some other action.

I have tried:

With Sheet3 (and With Worksheets("LogFile"))

If .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment.Visible = Visible Then
If .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment.Text = "" Then
 If Not (.Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment) Is Nothing Then
and a few others

End With


Maybe there was some combination that I missed, but each of these keep erroring out.  In using VBA, what is the best syntax to use to determine if a Comment already exits in the in the Cell:

.Cells([nSht3LastRow], [nBlocksCol]).

 There are values in both of the variables. Row = 15 Column = 15.
0
Comment
Question by:Cook09
  • 10
  • 6
  • 3
  • +1
20 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35506534
Please try like this.

jppinto
If Sheets("Sheet3").Cells([nSht3LastRow], [nBlocksCol]).Comment.Text = "" Then
   MsgBox "Cell Has No Comment"
Else
   MsgBox "Cell Has comment"
End If

Open in new window

0
 
LVL 15

Expert Comment

by:gplana
ID: 35506539
Have you tried this one?:

If .Range(.Cells(1, 1)).Comment.Text <> "" Then


which looks if there is a comment on Cell A1.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35506556
Here's a little code for your to try it...
Sub HasComment()
    On Error GoTo nocomment
    If Not ActiveCell.Comment.Text = "" Then
        MsgBox "Cell Has comment"
    End If
    Exit Sub
nocomment:
    MsgBox "Cell Has No Comment"
End Sub

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35506647
Cook09,

If the cell has no comment, then the approaches above will generate a runtime error.

If Not .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment Is Nothing Then
    'code for if comment is present
Else
    'code for no comment
End If

Open in new window


Patrick
0
 

Author Comment

by:Cook09
ID: 35506667
On the first on there was a run-time error:
   "The item with the specified name wasn't found."

The Second one also errors:
Method 'Range' of Object' _Worksheet' Failed

On the the third, since it's being done in VBA, there really is no ActiveCell, although, I did attempt to make it Active at one point and it still errored.  Not with yours, but with another one.
0
 

Author Comment

by:Cook09
ID: 35506695
Patrick
I still get the Method of Range Error...do I need to put On Error Resume Next?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35506702
Please check the attached sample file. Select a cell and click the button to test. Test on cells that have comments and on cells that don't have. I tested the code and it works fine for me.

jppinto
Check-Comments-Exist-VBA.xlsm
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35506852
Cook09,

Please post the code exactly as you tried it.

Patrick
0
 

Author Comment

by:Cook09
ID: 35506884
This what I've been trying:
With Sheet3
If Not .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment Is Nothing Then
Debug.Print "No Comment"
Else
Debug.Print "Comment In Cell"
End If
End With

Open in new window

0
 

Author Comment

by:Cook09
ID: 35506953
jppinto,

I tied it and it does work.  I copied it to a Utility Mod, selected a cell and it worked.  I guess the trick, is using VBA it make a cell Active and run the program as such.  I did try the ActiveCell after doing a:

.Range(.Cells([nSht3LastRow], [nBlocksCol])).Activate

and could not get it to work.  Maybe I'll try the above and substitute it into yours.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35506961
Try:

With Sheet3
If Not .Cells([nSht3LastRow], [nBlocksCol]).Comment Is Nothing Then
Debug.Print "No Comment"
Else
Debug.Print "Comment In Cell"
End If
End With

Open in new window

0
 

Author Comment

by:Cook09
ID: 35506986
Patrick,
Yes, I'll try that.

jppinto,

I tried
With Sheet3
.Range(.Cells([nSht3LastRow], [nBlocksCol])).Activate
End With

and it failed with the rest of the code.
0
 

Author Comment

by:Cook09
ID: 35507054
Patrick,
The two times I tried It, the procedure actually works in the reverse.  If there is a Comment, it Prints the first one.  When I deleted the Comment, it went to the second one.  I'll retry, but could it just be reversed in what it prints?
Ron
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 35507100
Ron,

Apologies.  I had my two Debug.Print statements swapped.

:)

Patrick
0
 

Author Comment

by:Cook09
ID: 35507149
Well, when all is said and Done, yours was the only one I got to work. It's interesting that in searching the Net, all the examples used were for global comments on a Sheet or Workbook.  None addressed a single cell, except to .AddComment or .Delete.

I will add this to my personal Knowledge Base.  But, do you know why the syntax is so particular with this one property?

Ron
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35507179
Ron,

It is because there is a Comment object, and if you try to address that object's properties (such as Text) when the object does not itself exist, you will get a runtime error.

That is, when you try a stement like this:

If MyRange.Comment Is Nothing

Open in new window


That Comment property is returning a Comment object.  The line above is OK, because all it does is check existence of the object.  If you try to go another layer down, and access properties of the Comment object, you get an error if that object does not exist.

Patrick
0
 

Author Closing Comment

by:Cook09
ID: 35507185
It's sometimes good having a Savant assisting in what would appear to be a simple thing on the surface.  Thanks for helping me through.

I appreciate the other two Experts and what they attempted to bring to this, but it is far less obvious than what would be assumed.
0
 

Author Comment

by:Cook09
ID: 35507231
Patrick,
I will also assume that there is no way around the error if one does attempt to access one of the properties?

I think, that in some literature somewhere, when trying to access a property that also could be an Object one could use something such as:

Range.Objects.Object.Properties

Is that correct or was I thinking about something else?

Ron
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35508011
Ron,

You could use an error handler, such as jppinto demonstrated, or even simply On Error Resume Next to ignore the error.

As for Range.Objects.Object.Properties, I am not aware of anything like that, but that does not necessarily mean that it doesn't exist.

Glad to help,

Patrick
0
 

Author Comment

by:Cook09
ID: 35508034
Patrick,

The On Error Resume Next doesn't work as it defaults to True, but there could be others that may work.  Thanks again.
Ron
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question