How do I set the list of a dependant combobox in a userform in word to a range in excel based on an independent combobox

Im trying to set the list of a dependant combobox in a userform in a word document to a range in excel that will change based on an independent combobox. There will be over 100 options in the independent combobox so i didn't want to write code for each option. Each range will also have different total amounts of columns to cover.

This is what I've tried so far. I've removed info that I can't share here but the code is able to access the excel spreadsheet. The independent combobox is called CompanyName, the dependent is Attention.

Private Sub CompanyName_Change()

    Attention.Clear
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.Workbooks.Open("FileLink")
    Set d = xlBook.Sheets("Sheet2").Cells.Find(what:=Me.CompanyName.Value, LookIn:=xlValues, LookAt:=xlWhole)
    
    For Each rng In xlBook.Sheets("Sheet2").Range("A2", "A29")
        If rng = Me.CompanyName.Value Then
            Attention.List = xlBook.Sheets("Sheet2").Range("A2:A", Range("A2").End(xlToRight)).Value
        End If
    Next rng
    
    xlBook.Close savechanges:=True
    xlApp.Quit

End Sub

When I run it i get run time error '1004': Application- defined or object-defined error. It highlights the Attention.List line when I click Debug. Any help would be much appreciated.

Solution

The script is executed in Word, so you need to qualify the Excel Range object with its parent object, xlBook.Sheets("Sheet2"). Additionally, xlToRight is a constant specific to Excel VBA and is not recognized by Word VBA.
To populate the List property of a ListBox, you need to use a N(rows)x1(column) 2D array. It seems you are attempting to load a row into the list, so the Excel worksheet function Transpose is used to convert the row into a suitable format for populating the ListBox.

    Const xlToRight = -4161 
    With xlBook.Sheets("Sheet2")    
        For Each rng In .Range("A2", "A29")
            If rng = Me.CompanyName.Value Then
                Attention.List = xlApp.Transpose(.Range("A2", .Range("A2").End(xlToRight)).Value)
                ' stop For looping after populating ListBox
                Exit For
            End If
        Next rng
    End With

Update: Dependent ComboBox

Question: Is there a way to make it so that the range it uses changes based on the first combobox choice.

After changing the first ComboBox, opening the Excel file to search for the company name and populate the list for the second ComboBox can be time-consuming. A more efficient approach is to declare a module-level variant array (arrData) and load the Excel table into this array in the UserForm's Initialize event.

Option Explicit

Dim arrData As Variant

' Initialize CompanyName
Private Sub UserForm_Initialize()
    Call LoadData
    Dim arr(), i As Long
    ReDim arr(1 To UBound(arrData) - 1)
    For i = 2 To UBound(arrData)
        arr(i - 1) = arrData(i, 1)
    Next
    Me.CompanyName.List = arr
End Sub

' load data into array, called by UserForm_Initialize
Sub LoadData()
    Dim xlApp As Object, xlBook As Object, isNewApp As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
        isNewApp = True
    End If
    On Error GoTo 0
    Dim sPath As String: sPath = ThisDocument.Path & "\"
    Set xlBook = xlApp.Workbooks.Open(sPath & "FileLink.xlsx")
    arrData = xlBook.sheets(1).usedrange.Value
    xlBook.Close False
    If isNewApp Then xlApp.Quit
End Sub

' Update list for Attention
Private Sub CompanyName_Change()
    Me.Attention.Clear
    Dim sCom As String: sCom = Me.CompanyName.Value
    Dim i As Long, j As Long, r As Long, arr()
    ReDim arr(1 To UBound(arrData, 2) - 1)
    For i = 2 To UBound(arrData)
        If sCom = arrData(i, 1) Then
            For j = 2 To UBound(arrData, 2)
                If Len(arrData(i, j)) = 0 Then
                    Exit For
                Else
                    r = r + 1
                    arr(r) = arrData(i, j)
                End If
            Next
            If r > 0 Then
                ReDim Preserve arr(1 To r)
                Me.Attention.List = arr
            End If
        End If
    Next
End Sub

' Close UserFrom
Private Sub CommandButton1_Click()
    Unload Me
End Sub

Sample data on worksheet