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.
Set rngActiveCell = ActiveCell
'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
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.
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.