CODEWITHSUNDEEP
excelvba
Ally
Ally

Reputation: 1

How to return a cell reference for a cell containing a particular comment?

I've written a VBA subroutine to search the comments (now called notes) of an Excel worksheet for a particular text string. I want the code to return the address (i.e. cell reference) of the cell containing a particular comment. However, the code as written seems to return the value in the cell rather than the address of the cell.

I've tried changing the line:

Set RefCell = cmt.Parent

in the below code, to:

Set RefCell = cmt.Parent.Address

as per another solution I found. However, this results in a Run-time error '424' 'Object Required'.

I appreciate any assistance anyone can provide.

Public Sub CommentLocator(Sht As Worksheet, RefCell As Range, CommentID As String)

    Dim Message As String, Title As String

    Dim cmt As Comment

    'On Error GoTo ErrorTrap

    'Clear previous value of RefCell
    Set RefCell = Nothing

    'Searches all comments on the worksheet for specific text string _
    and returns the range of the parent cell
    For Each cmt In Sht.Comments
        If cmt.Text = CommentID Then
            Set RefCell = cmt.Parent
            Exit Sub
        End If
    Next cmt

    If RefCell Is Nothing Then

        'Display error message
        Message = "Error!" & vbCrLf & _
            "No viable comments found on sheet '" & Sht.Name & "'" & vbCrLf & vbCrLf & _
            "Seek technical assistance."
        Title = "Error"

        MsgBox Message, vbExclamation + vbOKOnly, Title

    End If

Exit Sub

I expect RefCell to return the cell reference / address, but instead it returns the value in the cell.

Upvotes: 0

Views: 142

Answers (3)

Ally
Ally

Reputation: 1

Solved.

Basically, my issue was that I was defining my variable as a range, when it needed to be a string.

Thanks to those that commented - you've been really helpful to me in arriving at this solution.

Here's my solution in full:

'Require all variables to be declared
Option Explicit

'Public Variable Declarations
Public Message As String, Title As String


Public Sub CommentLocator(ByVal ObjectiveCommentID As String, _
           ByVal VariableCommentID As String, ByRef ObjectiveCell As String, _
           ByRef VariableCell As String)

    Dim cmt As Comment

    On Error GoTo ErrorTrap


    'Searches all comments on the active worksheet for specific text strings _
    represented by 'ObjectiveCommentID' and 'VariableCommentID' and returns the _
    addresses of the parent cells as strings
    For Each cmt In ActiveSheet.Comments
        If cmt.Text = ObjectiveCommentID Then
            ObjectiveCell = cmt.Parent.Address
        ElseIf cmt.Text = VariableCommentID Then
            VariableCell = cmt.Parent.Address
        End If
    Next cmt


    'Displays error message if no viable '$OBJECTIVE' comments found on sheet
    If ObjectiveCell = "" Then

        Message = "Runtime Error!" & vbCrLf & vbCrLf & _
            "No viable '" & ObjectiveCommentID & "' comments found on sheet" & vbCrLf _
            & "'" & ActiveSheet.Name & "'" & vbCrLf & vbCrLf & _
            "Check and update comments (notes) and try again."
        Title = "Error!"

        MsgBox Message, vbExclamation + vbOKOnly, Title

    End If


    'Displays error message if no viable '$VARIABLE' comments found on sheet
    If VariableCell = "" Then

        Message = "Runtime Error!" & vbCrLf & vbCrLf & _
            "No viable '" & VariableCommentID & "' comments found on sheet" & vbCrLf _
            & "'" & ActiveSheet.Name & "'" & vbCrLf & vbCrLf & _
            "Check and update comments (notes) and try again."
        Title = "Error!"

        MsgBox Message, vbExclamation + vbOKOnly, Title

    End If


Exit Sub


ErrorTrap:

    'Set calculation mode to auto & enable events
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

    'Enable screen updating & status bar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

    'Display error message
    Message = "Fatal Error!" & vbCrLf & _
        "Error in subroutine 'CommentLocator'." & vbCrLf & _
        "Seek technical assistance."
    Title = "Error!"

    MsgBox Message, vbExclamation + vbOKOnly, Title

End Sub

Upvotes: 0

Tom
Tom

Reputation: 9878

You need to use RefCell.Address to get the cell reference. Using Set on this line Set RefCell = cmt.Parent.Address expects an Object but you are passing it a String instead which is why you're getting the error

Try the following instead

If RefCell Is Nothing Then
    'Display error message
    Message = "Error!" & vbCrLf & _
        "No viable comments found on sheet '" & Sht.Name & "'" & vbCrLf & vbCrLf & _
        "Seek technical assistance."
    Title = "Error"

    MsgBox Message, vbExclamation + vbOKOnly, Title
Else
    MsgBox RefCell.Address
End If

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

You can use SpecialCells to loop through all cells with comments (notes):

On Error Resume Next
Set CommentCells = ActiveSheet.Range("A1").SpecialCells(xlCellTypeComments)
On Error GoTo 0
If CommentCells Is Nothing Then
    Exit Sub
End If

Then, to do whatever you want with the comments, loop though all cells with comments using:

For each RefCell in CommentCells
    'Do something 
Next RefCell

Upvotes: 0

Related Questions