Determine if ActiveCell is in a specific table

Question

I am trying to determine by VBA in Excel 2013 if the ActiveCell is not just in any table, but in a specific table.

Below is the code as is, but only detects ActiveCell being in any table. The commented out line is what I'm looking for, but obviously it doesn't work.

    ...
    Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
    'Set rng = Intersect(.EntireRow, ActiveCell.ListObjects("myTable").DataBodyRange)
    On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select the cell of a row within the consensus input table.", vbCritical, "Delete Evaluator"
        Else
    ...

Any suggestions on the right syntax for this?

Thanks!

Answer 1

To test if ActiveCell is in the body of Table1:

Sub qwerty()

   If Intersect(ActiveCell, ActiveSheet.ListObjects("Table1").DataBodyRange) Is Nothing Then
      MsgBox "activecell not in Table1"
   Else
      MsgBox "activecell in Table1"
   End If

End Sub

Answer 2

A more general solution, adaptable to other tables

Sub Demo()
    Dim r As Range
    Dim lo As ListObject

    Set r = ActiveCell
    Set lo = r.ListObject
    If Not lo Is Nothing Then
        Select Case lo.Name
            Case "Table1"
                If r.Row = lo.Range.Row Then
                    MsgBox "In Table1 Header"
                Else
                    MsgBox "In Table1 Body"
                End If
            Case "SomeOtherTable"
                '...
        End Select
    Else
        MsgBox "Not in any table"
    End If
End Sub

Answer 3

Generally, we're interested in work being performed within the DataBodyRange of a table and Excel provides us a shortcut for that area of a Table. For a table named "myTable", you directly access the DataBodyRange in code using [myTable].

Thus, for inclusive table location testing of the ActiveCell one could test as follows:

If Not Intersect(ActiveCell, [myTable]) Is Nothing Then