VBA to find if a cell in a range has a comment

[Printmark]

I'm in need of a VBA Solution. I can't figure out how to have VBA check a named range "UnitPrice" to see if any of the cells in that range contain a comment. If not, then call module InsertRowsSP, if so, then display message box and end sub.

Basically, if the unit price has been lock or had a override applied (which adds a comment to the cell automatically), then no new rows can be added.

Below is the code I am trying, but its not working right.

Sub FindCommentsSp()
Dim Targetcells As Range
Dim MyRange As Range
Set MyRange = Range("SpUnitPrice")
On Error Resume Next
Selection.SpecialCells(xlCellTypeComments).Select
Set Targetcells = Selection
If Application.Intersect(MyCell, Targetcells) Is Nothing _
Then Call InsertRowsSP _
Else MsgBox ("Cannot add rows after price is locked or override is used")
Sheets("Sp Est").Activate
End Sub

Thanks
Mark

 

[AlphaFrog]

Sub FindCommentsSp()
    Dim rngComments As Range
    
    On Error Resume Next
    Set rngComments = Range("SpUnitPrice").SpecialCells(xlCellTypeComments)
    On Error GoTo 0
    
    If rngComments Is Nothing Then
        Call InsertRowsSP
    Else
        MsgBox "Cannot add rows after price is locked or override is used"
    End If
    Sheets("Sp Est").Activate
    
End Sub

 

[Printmark]

Thanks Alpha Frog, your solution works great!

I knew I was making it way to complicated. Then I got fustrated and tried all kinds of weird stuff.

I appreciate the help!

Mark

 

[Printmark]

Upon futher testing, it didn't work all that great after all.. However, I did figure it out with a few minor changes.

Sub FindCommentsSp()
Dim rngComments As Object
On Error Resume Next
Selection.SpecialCells(xlCellTypeComments).Select
On Error Goto 0
Set rngComments = ActiveCell.Comment
If rngComments Is Nothing Then
Call InsertRowsSP
Else
MsgBox "Cannot add rows afert price is locked or override is used"
End If
End Sub

Sorry, don't know how to get it in one of those fancy code boxes. Whats missing is the selection of the Range("SpUnitPrice"), although that is range I wanted to check. When I added that in, I got errors, so I'm just letting it check the whole sheet that the range resides in. Not great, but it works.

Mark