VBA Find and Return Table name from Dropdown Selection.

[Falko26]

Hey Team,

I have a Dropdown list in Range("I1") that represents all Table Headers shown in Column B. What I would like to do is select one of the Table Headers from the dropdown list then run a VBA Macro to search column B for said Header. Once found and selected then offset selection down into the table and return the Table Name (List Object) of current selection to Range("J1").

Anyone have any ideas on how to do that?

 

[JGordon11]

try this in a regular module

Function TableName() As String
    Dim tbl
    For Each tbl In ActiveSheet.ListObjects
        With tbl
            If Not Intersect(.Range, Selection(2, 1)) Is Nothing Then
                TableName = .Name
                Exit Function
            End If
        End With
    Next
End Function

and this in your worksheet module:

Private Sub Worksheet_Change(ByVal TableTitle As Range)
    If TableTitle.Address <> "$I$1" Then Exit Sub
    Dim x As Range
    Set x = [B:B].Find(What:=TableTitle, After:=[B1], LookIn:=xlFormulas2, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False)
    If Not x Is Nothing Then
        x.Select
        [j1] = TableName()
        ActiveWindow.ScrollRow = Selection.Row
    Else
        MsgBox "Not found: " & TableTitle
    End If
End Sub