ComboBox in VBA for excel having limitations. Searching becomes difficult

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
688
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

Code:
'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
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
OMG
Nevertheless i tried the following but with Error it Blasts off and goes of out EXCEL

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top