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

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
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. NoNameAdd
1Caroline Martin2007 Excel Road
2Brian Green2010 VBA Road
3Amy Baker2013 Microsoft Street
4Susan Miller2013 Excel Street
5John SmithTXS
6Brian GreenNSW
7Caroline MartinExcl Road

<tbody>
</tbody>

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

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
Code:
ComboBox2.Value = ComboBox2.List(0)
in place of
Code:
ComboBox2.SetFocus
SendKeys "%{Down}"
to show the first address in the box.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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