ComboBox in VBA for excel having limitations. Searching becomes difficult

[NimishK]

Operational Issue
1. I've defined row source of combox having 30 items. So range from A1: A30. it all 30 items. As per the rows defined from the sheet. When searched or simply typed in it searched upto its limitation i.e not more than 10 items. I dont understand why.
Though I have defined Listrows as 8. As this height of Combobox looks decent if i define listrows as 30 then Combobox looks very taller.

What happens is When typing a value of a cell in the combobox if that value is in 11th row in combobox it will not show.

How can we cross this limitation or ATLEAST limit upto range of cells or rows defined in the column

Also click event of combobox shows the item of 10th row and not further. I don't know why

Tool ComboBox Properties

DragBehaviour = 0 -frmDragBehaviourDisabled
DropButtonStyle = 1 -frmDropButtonStyleArrow
EntryFieldBehaviour = 0 -frmEntryFieldBehaviour

MatchEntry= 1frmMatchEntryComplete

Displaying some property of combobox would be of rather some help for correction and resolving the same
Using Excel2013

'In userform
Private Sub UserForm_Initialize()
With ComboBox1
    .RowSource = "Sheet1!A2:A30"
 End With
End Sub


'in Combobox Click Event
Private Sub ComboBox1_Click()
Dim idx As Long
          idx = ComboBox1.ListIndex
          If idx <> -1 Then
              ComboBox1.Text = Worksheets("Sheet1").Range("A" & idx + 2).Value
           End If

Some thread also mention for searching in combobox dont code in change event.
Then for searching which is the event that coding should be done

Regards
NimishK

 

[NimishK]

OMG
Nevertheless i tried the following but with Error it Blasts off and goes of out EXCEL

Private Sub Combobox1_AfterUpdate()
Dim sCell As Range
Dim idx As Long
Dim lastRow As Long

Dim r As Excel.Range
idx = ComboBox1.ListIndex
lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).row

Set r = Sheets("Sheet1").Range("A:A" & lastRow).Find(What:=combo1.Text, LookAt:=xlPart, MatchCase:=True)

If IsEmpty(r) = True Then
    MsgBox "No blank Enter Correct Text"
Else

For Each sCell In r.Cells
     If sCell.Value = ComboBox1.Text Then
         MsgBox Worksheets("Sheet1").Range("B" & r.row).Value
     Else
    MsgBox "You got it"
End If
Next sCell
End If

End Sub