[SOLVED] How to detect column name from current cell in an Excel table?

How to detect column name from current cell in an Excel table?

In an event triggered macro Worksheet_SelectionChange(ByVal Target As Range) I need to offer the user the possibility to insert a value generate by another macro. However this should only take place in one certain column of the table.

For sure I can detect the current cell's column with Target.Column but this just gives me the numeric columns adress relative to the entire sheet. If my Excel table does not start on first column A, then this value does not match the index number of my coumn relative to the Excel table.

I have sought in object Target.ListObject but this does not seem to have something like "CurrentColumName".

For sure I could detect the range of the table and with a couple of calculations find out the header lable of the current column, but isn't there a more easy way to tell me "in which column (name) of the current Excel table (if any) my target cell is located?"

My current solution reads:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 7 And Target.Value = "" Then Call MyContentGenMacro() End If End Sub

But this relates to the fixed columns number 7 relative to the worksheet and not the the Excel table. So if I would move my table by one column, the macro would fail.

Any solution ideas anyone?

Trying to detect the field header from the current (=target) cell if located in an Excel table, but I only can detect the absolute column number relative to the worksheet.

Solution

Something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.ListObject Is Nothing And Target.Count = 1 Then
        If Intersect(Target.EntireColumn, Target.ListObject.HeaderRowRange) = "Your desired Column Header" Then
            
       MsgBox "Run your macro"

        End If
    End If
End Sub