When loading my userform in word i get Run-time error 381: Could not set the list property. Invalid Property array index. The aim is to generate a userform with a dependent combobox (Attention) and independent combobox (CompanyName) grabbing info from a separate excel spreadsheet
I don’t know whats wrong with my code and how to fix it.
Option Explicit
Private oVars As Variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim c As Excel.Range
Dim arrData As Variant
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
Caption = "Fill Me Out Please"
Label1.Caption = "Company Name"
Label2.Caption = "Attention of"
Label3.Caption = "Signature"
Label4.Caption = "Drawings"
Label5.Caption = "Calculation"
Label6.Caption = "Design Criteria Number"
Label7.Caption = "Rev"
Label8.Caption = "Rev"
Label9.Caption = "Design Criteria Dotpoints"
Label10.Caption = "Exclusions"
CommandButton1.Caption = "OK"
CommandButton2.Caption = "Cancel"
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("FileName.xlsx")
CompanyName.List = xlBook.Sheets("Sheet1").Range("A2:A29").Value
SigName.List = xlBook.Sheets("Sheet3").Range("A2:A29")
xlBook.Close savechanges:=True
xlApp.Quit
End Sub
Sub LoadData()
Dim 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
Set xlBook = xlApp.Workbooks.Open("FileName.xlsx")
arrData = xlBook.Sheets(2).UsedRange.Value
xlBook.Close False
If isNewApp Then xlApp.Quit
End Sub
Private Sub CommandButton1_Click()
Set oVars = ActiveDocument.Variables
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("FileName.xlsx")
Set c = xlBook.Sheets("Sheet1").Cells.Find(what:=Me.CompanyName.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
oVars("CompanyName").Value = c
c = c.Offset(0, 1)
oVars("Address").Value = c
c = c.Offset(0, 2)
oVars("Suburb").Value = c
c = c.Offset(0, 3)
oVars("State").Value = c
c = c.Offset(0, 4)
oVars("PostCode").Value = c
c = Me.CompanyName.Value
End If
Set c = xlBook.Sheets("Sheet3").Cells.Find(what:=Me.Sig.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
oVars("SigName").Value = c
c = c.Offset(0, 1)
oVars("Position").Value = c
c = c.Offset(0, 2)
oVars("Qualification").Value = c
c = Me.CompanyName.Value
End If
xlBook.Close savechanges:=True
xlApp.Quit
ActiveDocument.Fields.Update
Set oVars = Nothing
Unload Me
End Sub
Private Sub CommandButton2_Click()
'User has cancelled so unload the form
Unload Me
End Sub
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
New contributor
ZacJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1