VBA ActiveX dynamic ComboBox reduces ListRows to 1

Question

I am trying to get a VBA ComboBox to dropdown and display only those items which match or partially match the typed string.

For this purpose, I have set up a ComboBox KeyUp event manager, as follows:

Public Sub TempCombo_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    Select Case KeyCode

        Case 9
        'If TAB is pressed, then move one place right
            ActiveCell.Offset(0, 1).Activate

        Case 13
        'If Enter is pressed, then move one place down
            ActiveCell.Offset(1, 0).Activate

        Case Else
        'Otherwise, filter the list from the already entered text

            Dim x As Long

            OriginalValue = Me.TempCombo.Value

            'Remove items from the ComboBox list
            If Me.TempCombo.ListCount > 0 Then

                For i = 1 To Me.TempCombo.ListCount

                    Me.TempCombo.RemoveItem 0

                Next

            End If

            'If any part of any element from the 'FullSource' array matches the so far typed ComboBox value, then include it in the list for dropdown
            For x = 1 To UBound(FullSource)

                Typed_Value = "*" & LCase(OriginalValue) & "*"

                If LCase(FullSource(x)) Like Typed_Value Then

                    Me.TempCombo.Object.AddItem FullSource(x)

                End If

            Next

            Me.TempCombo.Value = OriginalValue

            Me.TempCombo.ListRows = 12

            Me.TempCombo.DropDown

    End Select

End Sub

The code seems to do the filtering fine. But the dropdown list height is only one unit tall. I have to scroll through this small box, using the mouse buttons.

Why the dropdown list reduces in size is a mystery to me, and I'd appreciate if any light can be thrown on this. Perhaps there is some setting that I am overlooking.

Thanks

Answer 1

You can use Me.TempCombo.Height = 15 to set the height.

If it doesn't work, you are probably running into ActiveX control instability issues. Refer to Excel VBA ComboBox DropDown Button Size--changed itself to use form controls instead of ActiveX.

Dynamically adjusting the width of a combobox in Excel VBA for more details on setting this dynamically.