Problem with populating ComboBox list

Hello,

I am having a problem populating a list using the following code:
------------------------------------------------------------------------------------------------------------------------------------------
Private Sub savedQuoteDetails_DropButtonClick()

savedQuoteDetails.List = Sheets("Input_Sheet_List").ListObjects("Input_Sheet_List").ListColumns(1).DataBodyRange.Value

End Sub
-------------------------------------------------------------------------------------------------------------------------------------------
The combo box = savedQuoteDetails
The sheet = Input_Sheet_List
The table = Input_Sheet_List

I am trying to populate this list from the 1st column of the table.

The code works perfect as long as there are at least 2 rows of data, however if I clear the table or only have 1 row of data in the table the code does not work. I get a "Run-time error '381': Could not set the List property. Invalid property array index." message.

Can this issue be resolved?

Thanks in advance,

Greg

Hi Greg,

It can indeed be resolved. One way would be to test how many rows of data are in the table and add them in one of two ways depending on the result.

Please Login or Register  to view this content.

BSB

Hello. Try it with:

Please Login or Register  to view this content.

Thank you for your suggestion BadlySpelledBuoy, it is much appreciated. When I run the code I get a "Compile error: Expected Function or variable" message and it highlights the ".Additem ="

Thank you beyond Excel. I don't get any errors with your code, however I am unable to select anything from the combo box.

Originally Posted by GregMachar

... however I am unable to select anything from the combo box.

Mmm... that's "another movie", hehehe
I don't know the source of this other problem, so you should upload a reduced model of your workbook to the forum to get a closer look at the problem you describe.

GregMachar,

Do you need it via DropButtonClick event?
This is Userform Initialize event instead.

Please Login or Register  to view this content.

Brilliant jindon... I copied this code into my DropButtonClick event and it seems to be working beautifully. Thank you so much for your help!

jindons solution has fixed that event (A big thank you to jindon!) however I am now having the same issue with the events to make the combo box searchable.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub savedQuoteDetails_Change()

Dim i As Long

If Not IsArrow Then
With Me.savedQuoteDetails
.List = Sheets("Input_Sheet_List").ListObjects("Input_Sheet_List").ListColumns(1).DataBodyRange.Value
.ListRows = Application.WorksheetFunction.Min(20, .ListCount)
.DropDown
If Len(.Text) Then
For i = .ListCount - 1 To 0 Step -1
If InStr(1, .List(i), .Text, vbTextCompare) = 0 Then .RemoveItem i
Next
.DropDown
End If
End With
End If

End Sub

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub savedQuoteDetails_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
IsArrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
If KeyCode = vbKeyReturn Then Me.savedQuoteDetails.List = Sheets("Input_Sheet_List").ListObjects("Input_Sheet_List").ListColumns(1).DataBodyRange.Value
End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Can anyone see a way to fix this?

Better upload a workbook and explain what you are trying to do, rather than posting non workbokng codes.

It will be totaly a guess work for us.