VBA UserForm Issue - Could not set the list property. Invalid property array index

Question

The below code works as long as there are more than one instance of the search criteria. However, if there is only one row that is listed as the what in the find function I receive the error "Could not set the list property. Invalid property array index"

Private Sub UserForm_Initialize()

Dim iRow As Integer, iMax As Integer

iRow = Cells.Find(What:="New Jersey Audit Adjustment", _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Row

iMax = Cells.Find(What:="New Jersey Audit Adjustment", _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False).Row
 
Me.ComboBox1.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
Me.ComboBox2.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
Me.ComboBox3.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
Me.ComboBox4.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
Me.ComboBox5.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
    
End Sub

The error occurs here Me.ComboBox1.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value if I have one row listed with "New Jersey Audit Adjustment"

From the Documentation: "Use List to copy an entire two-dimensional array of values to a control. Use AddItem to load a one-dimensional array or to load an individual element." The error is due to Range.Value returning a single value, which is then rejected by List as an invalid input. — Toddleson
– Toddleson, Commented Jan 14, 2022 at 20:00

EvR · Accepted Answer · 2022-01-14 20:33:31Z

1

When your range contains one cell, the .value will give you a value instead of an array. As the .list expects an array you could fill an array with one element or use addItem (see below)

If Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Cells.Count = 1 Then
    Me.ComboBox1.AddItem Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
    
Else
    Me.ComboBox1.List = Worksheets("C-Proposal-19").Range("B" & iRow & ":B" & iMax).Value
    
End If

edited Jan 14, 2022 at 20:33

answered Jan 14, 2022 at 20:00

EvR

3,5082 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA UserForm Issue - Could not set the list property. Invalid property array index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related