vba 在excel中使用VBA查找活动单元格的列标题名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7643652/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding Active cell's column header name using VBA in excel
提问by kinkajou
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
采纳答案by chris neilsen
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
回答by Mauro Haller
strCurrentColName = Cells(ActiveCell.ListObject.Range.Row, ActiveCell.Column).Value
回答by Eric D.
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 更改为它们碰巧所在的任何行。
回答by brettdj
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 行，您将使用Cells(2, ActiveCell.Column)etc
[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
