Getting the Current Table Name

[Jeffrey Mahoney]

I'm starting to see the value of using Excel Tables rather than dynamic named ranges. I know I can get ranges and headers from the table by direct reference. How do I get the name of the current Table?

I have 3 sheets, each with an Excel Table laid out with the exact same headers. Open Items, Approved Items, Completed Items. Lots of Sheet triggers are built in and I need to know what table name is from the target cell.

So I can use it like this:

TblName = CurrentTable                                       <<<< Unknown
Set R = Range(TblName & "[[Quote]:[Doc]]"

Sub Macro9()
  Dim R As Range

  Range("ApprovedItems_tbl[[Quote]:[Doc]]").Select

  Set R = Range("ApprovedItems_tbl[[Quote]:[Doc]]")

End Sub

[/Quote]

 

[Jeffrey Mahoney]

The column header "Vendor" was double checked for spelling. Run-time error '1004': Application-defined or object-defined error

 

[Jeffrey Mahoney]

The only way I seem to be able to set a group of columns into a single range is using this function.

Sub Test()
  Dim Tbl As ListObject
  Dim LC As ListColumns
  Dim u As Range
  
  Set Tbl = ActiveCell.ListObject
  Set LC = Tbl.ListColumns
  
'  LC("Site Name").DataBodyRange.Select
  Set u = GetRangeFrom2TblCols(Tbl, "Site Name", "Vendor")
  u.Select
  
  'Range(LC("Site Name").DataBodyRange, LC("Vendor").DataBodyRange).Select     '<<<<<<< Error
  'Debug.Print LC("Site Name").Index
  
End Sub

Function GetRangeFrom2TblCols(Tbl As ListObject, FCStr As String, LCStr As String) As Range
  Dim FCN As Long
  Dim LCN As Long
  Dim X As Long
  Dim u As Range
  
  FCN = Tbl.ListColumns(FCStr).Index
  LCN = Tbl.ListColumns(LCStr).Index
  If FCN > LCN Then
    X = FCN
    FCN = LCN
    LCN = X
  End If
  
  Set u = Tbl.ListColumns(FCStr).DataBodyRange
  For X = FCN + 1 To LCN
    Set u = Union(u, Tbl.ListColumns(X).DataBodyRange)
  Next X
  
  Set GetRangeFrom2TblCols = u
    
End Function

 

[Fluff]

You're code works for me, so not sure what is going on.
If the column name was wrong I'd expect an RTE 9, so that's not the problem.

 

[Jeffrey Mahoney]

Weird. I tried several tables and got the same error. It wasn't until I changed the code below when it worked.

ActiveSheet.Range(LC("Site Name").DataBodyRange, LC("Vendor").DataBodyRange).Select

 

[Fluff]

Do you have the code in sheet module, rather than a regular module?

 

[Jeffrey Mahoney]

Yes

 

[Fluff]

That would be the reason.
Any unqualified ranges in a sheet module refer to that sheet, not the activesheet.

 

[Jeffrey Mahoney]

Anyway, you helped me over the hump of learning the steps to get parts of the table through VBA. When I first saw the formulas that tables wanted to use it was foreign, so I've been reluctant to see the advantages until recently. This particular project I'm working on has been changed over completely to an Excel Table structure. Very easy to reference, no need to create dynamic ranges, and the formulas in the tables are not susceptible to corruption when users move cells around.

The one disadvantage is that if a column header name is changed, my code doesn't work because I'm making references by column name rather than column number.

 

[Fluff]

You're welcome & thanks for the feedback.