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.
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.