Page 1 of 3 123 LastLast
Results 1 to 10 of 23

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

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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 by SamDsouza; Sep 22nd, 2019 at 12:45 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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-...ues-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-...-criteria.html
    Last edited by jasonb75; Sep 22nd, 2019 at 03:33 AM.

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Sep 22nd, 2019 at 08:25 AM.

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by SamDsouza View Post
    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.

  5. #5
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Sep 22nd, 2019 at 10:36 AM.

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  7. #7
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,876
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    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. No Name Add Combo Formula in D2 copied down
    2
    1
    Caroline Martin 2007 Excel Road Caroline Martin 2007 Excel Road =B2&REPT(" ",30-LEN(B2))&C2
    3
    2
    Brian Green 2010 VBA Road Brian Green 2010 VBA Road
    4
    3
    Amy Baker 2013 Microsoft Street Amy Baker 2013 Microsoft Street
    5
    4
    Susan Miller 2013 Excel Street Susan Miller 2013 Excel Street
    6
    5
    John Smith TXS John Smith TXS
    7
    6
    Brian Green NSW Brian Green NSW
    8
    7
    Caroline Martin Excl Road Caroline 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
        ComboBox1.List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 3).Resize(, 1).Value
        ComboBox1.MatchEntry = fmMatchEntryNone
    End Sub
    4. The rest of your code requires amending to work with the single column combobox

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,876
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    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 by Yongle; Sep 23rd, 2019 at 01:18 AM.

  10. #10
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Sep 23rd, 2019 at 01:27 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •