CODEWITHSUNDEEP
excelvba
Firefighter1017
Firefighter1017

Reputation: 96

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. enter image description here 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

Upvotes: 1

Views: 4428

Answers (1)

PatricK
PatricK

Reputation: 6433

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

Upvotes: 5

Related Questions