Why Combobox displays its 1st index value of Duplicate items which are repeated and why not for the duplicate item clicked on

[SamDsouza]

Hello
I want to know why Combobox displays its first index value of 1 st item when searched if the items are duplicated repeated. Why cant it display its index value of its respective repeated item clicked on

Sr. No	Name	Add
1	Caroline Martin	2007 Excel Road
2	Brian Green	2010 VBA Road
3	Amy Baker	2013 Microsoft Street
4	Susan Miller	2013 Excel Street
5	John Smith	TXS
6	Brian Green	NSW
7	Caroline Martin	Excl Road

<tbody>
</tbody>

Above when Typing Brian Green It displays Two times Brian Green But when selecting 2^nd Brian Geen
It displays Add as 2010 VBA Road and not NSW even though Sr. No is different.

Option Explicit
Private IsArrow As Boolean

Private Sub UserForm_Initialize()
    ComboBox1.List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 1).Value
    ComboBox1.MatchEntry = fmMatchEntryNone
End Sub
 
Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 1).Value
       
        If .ListIndex = -1 And Val(Len(.Text)) Then
            For i = .ListCount - 1 To 0 Step -1
               If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
End Sub
 
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
    If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Worksheets("Sheet1").Range("B1").CurrentRegion.Offset(1, 0).Value
End Sub

Private Sub ComboBox1_Click()
    Dim f As Range
    If ComboBox1.ListIndex <> -1 Then
        Set f = Worksheets("Sheet1").Range("B:B").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
        TextBox1.Value = f.Value
        MsgBox "Row : " & f.Row & ". original index number : " & f.Row - 2
    End If
End Sub

SamD

 

[jasonb75]

Sorry, I don't follow what you're saying in your last post.

Are you saying that the focus being moved to the second box is making it difficult to select from the first box?

 

[SamDsouza]

Yes because of focus moved on second box. Any ideas how this can work smoothly ?

My question was is there any ComboBox property where up and down buttons were seen when list was more than one item.
I was working to search for the same on Userform. This appeared as i did not save the same and closed it and trying to search for that particular property option. Well
Still exploring the same if found will let you know

 

[jasonb75]

You will not be able to view the dropdown without setting focus on box 2. If you want to keep focus on box 1 then the best you will get is to use

ComboBox2.Value = ComboBox2.List(0)

in place of

ComboBox2.SetFocus
SendKeys "%{Down}"

to show the first address in the box.