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.
ZacJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
-
The script is executed in Word, so you need to qualify the Excel
Rangeobject with its parent object,xlBook.Sheets("Sheet2"). Additionally,xlToRightis a constant specific to Excel VBA and is not recognized by Word VBA. -
To populate the
Listproperty 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 functionTransposeis 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
4