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
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox Target.Value
MsgBox ActiveCell.Address
End Sub
VBA Coding Made Easy. If you need to get the active cell’s column or row from VBA, use this code: Active Cell Column. For the cell’s column: 1. 2. 3. PublicSubActiveColumn() MsgBox ActiveCell.
Reference Table Column by Column Header Name with VBA in Excel 2. Embed VBA to Select Column of a Table without Header by Column Header Name 3. VBA to Insert New Row in the Table with the Reference of Column Header Name 3.1. With the ListColumns Property 3.2. With the Intersect Method 4.
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
Excel VBA Find (Cell with) Value in Table Column To find a cell with a numeric value in an Excel Table column, use the following structure/template in the applicable statement: The following Sections describe the main elements in this structure. A ListColumn object representing the Excel Table column you search in.
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
strCurrentColName = Cells(ActiveCell.ListObject.Range.Row, ActiveCell.Column).Value
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With