office交流網--QQ交流群號

Access培訓群:792054000         Excel免費交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

穫取當前激活單元所在的Listobject 錶格的列名稱

2020-05-25 08:00:00
zstmtony
原創
187
穫取當前激活單元所在的Listobject 錶格的列名稱
Find column name of active cell within ListObject in Excel

Target 如果多於一箇單元格,隻有第一箇單元格被使用


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




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



2020年05年26日改進版

'穫取光標所在列名
Public Function gf_GetActiveColumnName(ByVal Target As Range)
    Dim lo As ListObject
    Set lo = Target.ListObject
    gf_GetActiveColumnName = ""
    If Not lo Is Nothing Then
        If lo.Active Then
            gf_GetActiveColumnName = lo.HeaderRowRange.Cells(1, Target.Column - lo.Range.Column + 1).Value
        End If
    End If
End Function
''穫取光標所在列名 另一種方法
'Public Function gf_GetActiveColumnName(ByRef Target As Range) As String
'    If Not Target.ListObject Is Nothing Then
'        gf_GetActiveColumnName = Intersect(Target.ListObject.HeaderRowRange, Target.EntireColumn).Value
'    Else
'        gf_GetActiveColumnName = ""
'    End If
'End Function


分享