在Excel中使用VBA查找活动单元的列标题名称

I have a table created from List of data. How to find the header text of each column

我有一个从数据列表创建的表。如何查找每列的标题文本

When I select the activecell's header is high lighted to orange but I want to retrieve that value using visual basic. I am able to find excel sheet's address but I need table's column header

当我选择activecell的标题高亮到橙色但我想使用visual basic检索该值。我能找到excel表的地址，但我需要表的列标题

   Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      MsgBox Target.Value 
      MsgBox ActiveCell.Address
   End Sub

4 个解决方案

#1

This will return the column header, if the passed cell is in a table

如果传递的单元格在表中，这将返回列标题

Function TableHeader(cl As Range) As Variant
    Dim lst As ListObject
    Dim strHeading As String

    Set lst = cl.ListObject

    If Not lst Is Nothing Then
        TableHeader = lst.HeaderRowRange.Cells(1, cl.Column - lst.Range.Column + 1).Value
    Else
        TableHeader = ""
    End If
End Function

#2

strCurrentColName = Cells(ActiveCell.ListObject.Range.Row, ActiveCell.Column).Value

#3

If by column header you mean the cell in the first row in the same column that a change has been made then you can construct the header using Cells, ie

如果按列标题表示同一列中第一行中的单元格已进行更改，则可以使用单元格构建标题，即

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    MsgBox Target.Value
    MsgBox ActiveCell.Address & vbNewLine & "Column header is " & Cells(1, Target.Column)
End Sub

If your header was in row 2 you would use Cells(2, ActiveCell.Column) etc

如果您的标题在第2行，您将使用单元格（2，ActiveCell.Column）等

[Updated after comment below]

[以下评论后更新]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng1 As Range
Set rng1 = Target.End(xlUp)
MsgBox "First value before a blank cell/top of sheet is " & rng1.Value
End Sub

#4

I was online searching for a good way to find the header value of any given cell in the table and after a little brainstorming came up with this working solution that I thought I would share.

我正在网上寻找一种很好的方法来找到表格中任何给定单元格的标题值，经过一些头脑风暴后我想到了这个可行的解决方案。

    Target.Offset(1 - Target.Row).Value

In the event that your headers aren't in row 1 then change the 1 to whatever row they happen to be in.

如果您的标题不在第1行，则将1更改为它们恰好位于的行。