Microsoft optimize

Use VBA to determine whether an active cell is in a table

As you update your applications to include table shortcuts, you may find that you need to determine whether the active cell is in a table. Here's a function that will help you determine that.

The table object feature is new to Excel 2007 and offers a useful tool for managing data. Sorting, filtering, and analyzing are all easier than ever using tables. As you update your applications to include table shortcuts, you may find that you need to determine whether the active cell is in a table. There's no property for that, but you can use the ListObject's Name property as follows:

Function IsActiveCellInTable() As Boolean

  'Function returns true if active cell is in a table and
  'false if it isn't.
    Dim rngActiveCell
    Set rngActiveCell = ActiveCell
    Debug.Print IsActiveCellInTable
    'Test for table.
    'Statement produces error when active cell is not
    'in a table.
    On Error Resume Next
    rngActiveCell = (rngActiveCell.ListObject.Name <> "")
    On Error GoTo 0
    'Set function's return value.
    IsActiveCellInTable = rngActiveCell
End Function

By default, this function returns False, but will return True when the active cell is part of a table. When this is the case, the expression

rngActiveCell.ListObject.Name <> ""

equals true - the name property isn't an empty string. When there's no table present, there's no table name. In this case, the code generates an error, but the On Error statements inhibit it and IsActiveCellInTable remains False. Ordinarily, I don't recommend generating an error on purpose, but occasionally, doing so can be helpful.

Call this function when you need to know if the active cell is an in a table and use the results, true or false, accordingly.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments