excelvba

Find column name of active cell within ListObject in Excel


I have a table defined in Excel as a ListObject

In VbA, I can access rows and columns using ListObject("table1").ListRows or ListObject("table1").ListColumns

In one of my subs, I wan't to know the active cell's column name. And because the columns could be moved around, I want this to be dynamic.

For example, in the previous image, let's say I clicked on a cell of the third column. I'd like to have a function that tells me the column name associated with that cell in the current ListObject.

In this case it would return Line Desc.

I tried to use a spy to find an object path that would allow me to figure out the column name, but I couldn't find any.

Before I start building a function that will do just that, I wanted to make sure I didn't miss anything in VbA objects repository.

Any idea if there is a built-in way to find the active cell's column name?

Thanks

Note: If I run out of time and create a function, I will post it here.

EDIT Here's what I have found so far:

Function ColumnName(byVal Target as Range)
    If Sheets("Equipements").Listobjects("tMain").Active then
        ColumnName = ListObjects("tMain").HeaderRowRange.Cells(1, Target.Column).Value
    End If
End Function

Solution

  • Possible issue with your method occurs when the ListObject doesn't start from Column A.

    Con: Fixed to the table "tMain" only, cannot use on any other tables.

    You can try this (if Target is more than 1 cell, only the top left cell is used):

    Option Explicit
    
    Function ColumnName(ByRef Target As Range) As String
        If Not Target.ListObject Is Nothing Then
            ColumnName = Intersect(Target.ListObject.HeaderRowRange, Target.EntireColumn).Value
        Else
            ColumnName = ""
        End If
    End Function