Ok, so there's ActiveCell and ActiveSheet...but how 'bout ActiveTable?

[Randall00]

Hi there!

This is about the ever-unfolding mysteries surrounding Tables from Office 2007 in VBA.

The lovely piece of code below does a fine job of removing the Table style from Table1 and converting it to a normal range:

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'remove table or list style
    oSh.ListObjects("Table1").Unlist
End Sub

It's the ListObjects("Table1") bit that I'm wondering about. Every piece of code related to tables that I can find seems to refer to them in this way, but the question is:

Is there a way to refer to the active table?

i.e.

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'remove table or list style
    ActiveSheet.ActiveTable??.Unlist
End Sub

I imagine this comes back to declaring variables in way that I don't quite understand--a point in the right direction would be appreciated, thanks!

 

[Randall00]

I'd best bump this and see if anyone's got any ideas. I don't think it's all that stupid a question.

 

[mattrindy]

In order for a table to be active, a cell inside of that table must be activated. Select a cell within the table and try this:
ActiveCell.ListObject.Range.Select
(This should select your table or whatever ListObject your active cell is a part of)

ActiveCell.ListObject.Unlist
(This Should remove only the table but not the cell values)

I was having the same issue just a second ago and that is what I came up with.
Hope it helps