CODEWITHSUNDEEP
vbaexcel
K.C. Grimes
K.C. Grimes

Reputation: 187

Determine if ActiveCell is in a specific table

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!

Upvotes: 13

Views: 19103

Answers (5)

FriskyKitty
FriskyKitty

Reputation: 53

I use the following line of code:

    ActiveCell.ListObject.Name

or sub:

    Sub IsActiveCellInTable()
    'If active cell in table then get name'
    Dim strTblName As String

        'Disable error checking'
        On Error Resume Next 

        strTblName = ActiveCell.ListObject.Name

        'Reset error checking'
        On Error GoTo 0 

        If strTblName <> "" Then
            MsgBox "Cell (" & ActiveCell.Address & ") is included in: " & strTblName
        Else
            MsgBox "Cell (" & ActiveCell.Address & ") is not included in table."
        End If

    End Sub

Upvotes: 0

gvkv
gvkv

Reputation: 1916

A Range object has a ListObject property that will return the table of the Range. All you have to do is to test if the cell is in any table:

If ActiveCell.ListObject Is Nothing Then
    ...

and see if it is in your specific table:

If ActiveCell.ListObject.Name = "MyTable" Then
    ...

and you're done!

Much cleaner than using Application.Intersect(...). chris neilsen's answer alludes to this as well.

Upvotes: 4

IronX
IronX

Reputation: 355

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

Upvotes: 7

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 8

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 14

Related Questions