ComboBox.DropDown ONLY SHOWS FIRST ITEM with TINY SCROLLBAR

**Guido Meng** · 03-25-2013, 12:05 AM

Combobox Dropdown List only shows first Item
Hi everyone!

I have the following code to update a combobox list as text is entered in the control:

  Private Sub CargaNOMBRES(Parcial As String)

    Dim ListSize As Long
    
    Dim Indice As Long
    
    Dim Rango As Variant


    With Worksheets("Ingreso Lecturas")    
        If .Cells(20, 2) <> "" Then        
            ListSize = .Range(.Cells(20, 2), .Cells(.Range("B:B").Rows.Count, 2)).Rows.Count                                             ' CALCULATING THE NUMBER OF ITEMS OF THE WHOLE LIST
            ListSize = ListSize - Application.WorksheetFunction.CountBlank(.Range(.Cells(20, 2), .Cells(19 + ListSize, 2)))            
            
            
            If Parcial = "" Then                                                                ' IF NO TEXT HAS BEEN ENTERED IN THE COMBOBOX, THEN CAPTURE ALL ITEMS IN THE RANGE
                Set Rango = .Range(.Cells(20, 2), .Cells(19 + ListSize, 2))    
                ComboBoxNOMBRES.List = Rango.Value            
                Set Rango = Nothing           
     
            Else                                                                                   ' ONLY ADD THE ITEMS THAT MATCH THE PARTIALLY ENTERED TEXT
            
                ComboBoxNOMBRES.Clear
                
                For Indice = 1 To ListSize                
                    If UCase(Mid(.Cells(Indice + 19, 2), 1, Len(Parcial))) = UCase(Parcial) Then                    
                        ComboBoxNOMBRES.AddItem .Cells(Indice + 19, 2)                        
                    End If                    
                Next Indice
                
            End If   
                                                                                                               
        End If
        
    End With

End Sub


Private Sub ComboBoxNOMBRES_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    Dim Parcial As String
    
    Dim Caracter As String
    
    
    If Len(ComboBoxNOMBRES.Text) = 3 Then                         ' FOR DEBUGGING PURPOSES ONLY
        
        FirstKey = FirstKey
            
    End If    

    
    If (KeyCode = 13) Then                                                 ' USER HAS COMPLETED THE ENTRY, THIS WORKS FINE!
    
        EventoNOMBRES        
        CapturaEdición = False        
        FirstKey = True
        
    End If
    
    
    If (UCase(Chr(KeyCode)) Like "[A-Z]") Or (KeyCode = 8) Or (KeyCode = 46) Then    ' USER IS MODIFIYING THE TEXT TO REFINE THE SEARCH

        CargaNOMBRES (ComboBoxNOMBRES.Text)                                                   ' PROCEDURE FILTERS LIST JUST FINE!    
        ComboBoxNOMBRES.ListRows = 8                                                                ' ATTEMPT TO INSTRUCT THE COMPUTER TO SHOW UP TO 8 ROWS OF THE LIST 
        ComboBoxNOMBRES.DropDown                                                                   ' DROP THE LIST TO SHOW UP TO 8 ROWS   ( THE LIST CONTAINS THE DESIRED ITEMS, PROBLEM IS ONLY ONE ROW, INSTEAD OF 8 IS SHOWN!
        
    End If
    
    
    KeyCode = 0

End Sub

Can anyone tell me what's the proble,? this thing it's driving me crazy!

**Guido Meng** · 03-25-2013, 03:15 PM

Hi again!

Just wanted to post a solution a found to the problem described in my previous thread. Only thing I did was to fill a unidimensional matrix (vector) with the filtered list and then assign the values of this matrix to the combobox list instead of adding the values one by one using the "AddItem" method. I have what I wanted but I'd like to know (for future reference) why in this case there's a difference between asigning values to a list via the additem method vs adding values via the combobox.List() = Matrix method. Here's the modifyied routine:



Private Sub CargaNOMBRES(Parcial As String)

    Dim ListSize As Long
    
    Dim FinalSize As Long
    
    Dim Indice As Long
    
    Dim Rango As Variant
    
    Dim Options() As String


    With Worksheets("Ingreso Lecturas")
    
        If .Cells(20, 2) <> "" Then
        
            ListSize = .Range(.Cells(20, 2), .Cells(.Range("B:B").Rows.Count, 2)).Rows.Count
        
            ListSize = ListSize - Application.WorksheetFunction.CountBlank(.Range(.Cells(20, 2), .Cells(19 + ListSize, 2)))
            
            
            
            If Parcial = "" Then
            
                Set Rango = .Range(.Cells(20, 2), .Cells(19 + ListSize, 2))
    
                ComboBoxNOMBRES.List = Rango.Value
            
                Set Rango = Nothing
                
            Else
            
                ComboBoxNOMBRES.Clear
                
                ReDim Options(ListSize)
                
                FinalSize = -1
                
                
                For Indice = 1 To ListSize
                
                    If UCase(Mid(.Cells(Indice + 19, 2), 1, Len(Parcial))) = UCase(Parcial) Then
                    
                        'ComboBoxNOMBRES.AddItem .Cells(Indice + 19, 2)          <-------------------------------    THIS INSTRUCTION NO LONGER EXECUTES
                        
                        FinalSize = FinalSize + 1
                        
                        Options(FinalSize) = .Cells(Indice + 19, 2)
                        
                    End If
                    
                Next Indice
                
                ReDim Preserve Options(FinalSize)
                
                ComboBoxNOMBRES.List() = Options            <-------------------------------------  THIS LINE REPLACES THE "AddItem"  INSTRUCTION
                
                
            End If
                    
                
                                                                                                               
        End If
        
    End With

End Sub

and that's all it took to make my routine work but Why???

Thanks for any comment on this regard !!!