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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
It was probably never designed to work in the way that you're trying to use it.

Normal practice would be to have only 1 'Brian Green' in the first combobox, then a second combobox with the 2 address options.

edit:- to populate the list in combobox1 see the answer here

https://www.mrexcel.com/forum/excel...te-userform-combobox-unique-values-range.html

for the second combobox, see Mike Rickson's answer here, use the item selected from combobox1 in place of nullstring.

https://www.mrexcel.com/forum/excel-questions/794425-populate-combobox-sheet-criteria.html
 
Last edited:
Upvote 0
Jasonb75

Thanks for sharing the links. I've gone through them. Both the threads show to load the items in Combobox when UF initilaized. Only thing two threads dont show the correct index values or respective index values when clicked for repeated items

Normal practice would be to have only 1 'Brian Green' in the first combobox, then a second combobox with the 2 address options.
Why should i have Two Comboboxes when coding done for one combobox as per my post #1

SamD
 
Last edited:
Upvote 0
Why should i have Two Comboboxes when coding done for one combobox as per my post #1
Because it will not work with one!

A combobox will not do what you want with repeated items, it is not designed to hold repeated items.
 
Upvote 0
Because it will not work with one!


A combobox will not do what you want with repeated items, it is not designed to hold repeated items.
OMG :(:( Don't tell me.

Combobox is one of the important tool required for at least list search. Manytimes We are dependant on this very much.

So are you telling that if items are repeated 25 times for eg you require 25 comboboxes or other items also repeated we require those nos. of Cmbxes. Hope i've undesrtood you correctly, if yes, then this is real unfortunate.
 
Last edited:
Upvote 0
No, you need 2 comboboxes regardless of the number of repeated items.

The first to give the list of names, with duplicates removed.
The second to replace the textbox in your userform, which would show the addresses (or required data) to go with the selected name.

So with your example, box 1 would show as below

Caroline Martin
Brian Green
Amy Baker
Susan Miller
John Smith
Caroline Martin

Then when Brian Green is selected, box 2 would show

2010 VBA Road
NSW

This is the closest that you will get to what you want.
 
Upvote 0
OK Thanks for the clarification

It seems no alternative

Then How do i achieve this ?

When Brian Green selected will other Combo drop down to display 2010 VBA Road : NSW or go on to 2nd combo click and will display
 
Upvote 0
I do not know if this of any help to you.

Consider using a SINGLE field to populate the combobox to avoid duplicated records

1. Concatenate the values in columns B & C to populate combobox
=B2&REPT(" ",30-LEN(B2))&C2

The "filler" in the formula is to help with presentation inside the combobox - I am guessing that 30 characters is more than the length of any single name in column B

Excel 2016 (Windows) 32 bit
A
B
C
D
E
1
Sr. NoNameAddCombo Formula in D2 copied down
2
1​
Caroline Martin2007 Excel RoadCaroline Martin 2007 Excel Road =B2&REPT(" ",30-LEN(B2))&C2
3
2​
Brian Green2010 VBA RoadBrian Green 2010 VBA Road
4
3​
Amy Baker2013 Microsoft StreetAmy Baker 2013 Microsoft Street
5
4​
Susan Miller2013 Excel StreetSusan Miller 2013 Excel Street
6
5​
John SmithTXSJohn Smith TXS
7
6​
Brian GreenNSWBrian Green NSW
8
7​
Caroline MartinExcl RoadCaroline Martin Excl Road
Sheet: Sheet1


2. Use a MONOSPACED font for the combobox ( Examples of MONOSPACED fonts Consolas, Courier New, Lucida Console )
- all characters are the same width which ensures that all addresses are lined up and makes combobox "appear" to contain 2 columns

3. Populate combobox from column D like this
Code:
Private Sub UserForm_Initialize()
    Dim cel As Range
    [COLOR=#8b4513]ComboBox1.List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 3).Resize(, 1).Value[/COLOR]
    ComboBox1.MatchEntry = fmMatchEntryNone
End Sub

4. The rest of your code requires amending to work with the single column combobox
 
Upvote 0
an afterthought ....

using this formula instead in column D
=B2&REPT(" ",30-LEN(B2))&"| "&C2

Spaces added after | purely for presentation inside combobox

then get name and address like this:
Code:
Dim v As Variant
    v = Split(ComboBox1.Value, "|")
    MsgBox "Name" & vbTab & v(0) & vbCr & "Addr:" & vbTab & Trim(v(1))
Trim removes those spaces
 
Last edited:
Upvote 0
Yongle Practical suggestion by you which i had thought of but then structure of the worksheet is disturbed allthough 3 columns at present as per #1

As per jasonB75 suggestion. Explored and Found the code to remove duplicates
Code:
Private Sub UserForm_Initialize()
 Dim LR As Long
 LR = Cells(Rows.Count, "B").End(xlUp).Row
ComboBox1.List = CreateArray(Range("B2:B" & LR))
End Sub

Function CreateArray(r As Range)
    Dim col As New Collection, c As Range, TempArray(), i As Long
    
    For Each c In r
        On Error Resume Next
        col.Add c.Value, CStr(c.Value)
        If Err.Number = 0 And Trim(c) <> "" Then
            ReDim Preserve TempArray(i)
            TempArray(i) = c.Value
            i = i + 1
        End If
        Err.Clear
    Next

    CreateArray = TempArray
    Erase TempArray
End Function
Only thing now is trying to add items in combobox2 with duplicates and non-duplicates seems difficult to incorporate in code as per #1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
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