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]

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

 

[SamDsouza]

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

 

[jasonb75]

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.

 

[SamDsouza]

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.

 

[jasonb75]

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.

 

[SamDsouza]

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

 

[Yongle]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Sr. No[/td][td]Name[/td][td]Add[/td][td]Combo[/td][td] Formula in D2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1​

[/td][td]Caroline Martin[/td][td]2007 Excel Road[/td][td=bgcolor:#C6E0B4]Caroline Martin 2007 Excel Road[/td][td] =B2&REPT(" ",30-LEN(B2))&C2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2​

[/td][td]Brian Green[/td][td]2010 VBA Road[/td][td=bgcolor:#C6E0B4]Brian Green 2010 VBA Road[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

3​

[/td][td]Amy Baker[/td][td]2013 Microsoft Street[/td][td=bgcolor:#C6E0B4]Amy Baker 2013 Microsoft Street[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

4​

[/td][td]Susan Miller[/td][td]2013 Excel Street[/td][td=bgcolor:#C6E0B4]Susan Miller 2013 Excel Street[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

5​

[/td][td]John Smith[/td][td]TXS[/td][td=bgcolor:#C6E0B4]John Smith TXS[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

6​

[/td][td]Brian Green[/td][td]NSW[/td][td=bgcolor:#C6E0B4]Brian Green NSW[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

7​

[/td][td]Caroline Martin[/td][td]Excl Road[/td][td=bgcolor:#C6E0B4]Caroline Martin Excl Road[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


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

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

 

[Yongle]

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:

Dim v As Variant
    v = Split(ComboBox1.Value, "|")
    MsgBox "Name" & vbTab & v(0) & vbCr & "Addr:" & vbTab & Trim(v(1))

Trim removes those spaces

 

[SamDsouza]

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

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