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

 

[Yongle]

Combobox2 is dependant on value in Combobox1

So try something like this (untested but provides a method for you to explore further) ...

Dim cel As Range
For each cel In Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 1).Resize( , 1 )
 If cel = Combobox1.Value Then
   Combobox2.AddItem cel.Offset(, 1)
 End If
Next cel

 

[SamDsouza]

Yongle

Thanks for this. but now only How to get ComboBox2.dropdown displayed if item Brian Green selected then Combo2 to display 2010 VBA Road: NSW
or full list displayed of combo2 of items selected in Combo1.

Combobox2 is dependant on value in Combobox1


So try something like this (untested but provides a method for you to explore further) ...
Code:
Dim cel As Range
For Each cel In Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 1).Resize(, 1)
If cel = ComboBox1.Value Then
ComboBox2.AddItem cel.Offset(, 1)
End If
Next cel

 

[Yongle]

i do not understand your question

 

[SamDsouza]

In Combox1 the Names are items of combobox1. So clicking on ComboBox1 down arrow key the Names are displayed.

When item or name clicked on ComboBox1 Combobox2 displays the address or Combobox2 items

So Each time clicked on ComboBox1 Combobox2 should display its Full List and not that i click every time on Combobox2 to check the available address or options to choose

HTC

*69*

 

[Yongle]

Can we go back to the beginning of the thread and start again

Is this what you want ?

Option Explicit
[COLOR=#ff0000]Private Rng As Range[/COLOR]
Private IsArrow As Boolean

Private Sub UserForm_Initialize()
    [COLOR=#ff0000]Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 1)
    ComboBox1.List = Rng.Value[/COLOR]
    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, 3).Value
End Sub
Private Sub ComboBox1_Click()
    [COLOR=#ff0000]Dim f As Range
    Dim x As Long
    x = ComboBox1.ListIndex + 1[/COLOR]
    If ComboBox1.ListIndex <> -1 Then
        Set f = Worksheets("Sheet1").Range("B:B").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
        TextBox1.Value = f.Value
        [COLOR=#ff0000]MsgBox Rng(x, 1), , "Name"
        MsgBox Rng(x, 2), , "Address"[/COLOR]
    End If
End Sub

EDIT
No this is not helping - SORRY
PLEASE IGNORE !

 

[Yongle]

I still do not understand why you have a problem


Enter Combobox1

Select name Caroline Martin from dropdown ()

Activate Combobox2
Dropdown list is auto-populated with
2007 Excel Road
Excl Road

Click on Excl Road

Your selection is Caroline Martin (Combobox1.Value) and Excl Road (Combobox2.Value)

 

[SamDsouza]

You are right.
if you look below code the Combobox1_Change() event is triggered with .Dropdown ie as when typing each letter The Combobox1 is displayed fully with all options.

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

In similar way when clicking on combobox1 i want directly Combobox2 to display and not every time click on cmbx2 and see the the items.
I know that the list is displayed but with 1st address and the full list is not diaplyed . For full list ive to click on cmbx2 down arrrow which is quite irritating.
If directly opened then List is fully opened /displayed for me to chose the direct option

 

[jasonb75]

You could add something like

   ComboBox2.SetFocus
   SendKeys "%{Down}"

to your code, note that the list will need to be populated based on the selection of box 1 before executing this.

 

[jasonb75]

There is room for improvement, but see if this does what you want.

Option Explicit

Private Sub ComboBox1_Change()
 Dim LR As Long
 LR = Cells(Rows.Count, "B").End(xlUp).Row
ComboBox2.List = CreateArray(Range("C2:C" & LR), ComboBox1.Value)
ComboBox2.SetFocus
SendKeys "%{Down}"
End Sub

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, Optional n As String)
    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
            If n <> "" Then
                If c.Offset(, -1) = n Then
                    ReDim Preserve TempArray(i)
                    TempArray(i) = c.Value
                    i = i + 1
                End If
            Else
                ReDim Preserve TempArray(i)
                TempArray(i) = c.Value
                i = i + 1
            End If
        End If
        Err.Clear
    Next

    CreateArray = TempArray
    Erase TempArray
End Function

 

[SamDsouza]

Jasonb75

Thanks for your last coding
with

ComboBox2.SetFocus
SendKeys "%{Down}"
This worked. But is there any ComboBox Property when Comobox2 hilited can still dropdown with option. While checking on userform it worked once but then i missed as on which option i clicked. Unable to recall