ComboBox: Searching its Original Index No. Value after value searched

[SamDsouza]

Hello

Can ayone help me to find original Combobox index Value even after i search. ?
in other words, when i search the list displays all the values, but clicking on the searched item it displays its index values as as per searched values.

What i want is when i search the value i want its original index number
A
1 Names
2 Smith *0
3 Jennifer *1
4 Jhon *2
5 Andrew *3
6 Bob *4
7 Celina *5
8 Sam *6
9 Catherine*7

in above range when clicked on combo i get index values from 0 to 7 repsectively.
suppose i type S the List displays Smith and Sam the index values of combobox i get is 0 and 1 rather than what is desired is to get 0 and 6
Kindly note that numbers 0 to 7 are index nos just for reference and same are not the values in Column B

Option Explicit
Private IsArrow As Boolean

Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).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_Click()

Dim idx As Long, strSearchValue As String
idx = ComboBox1.ListIndex
strSearchValue = ComboBox1.Text
 If idx <> -1 Then
        TextBox1.Value = Worksheets("Sheet1").Range("A" & idx + 2).Value
    End If
txtCbIndex.Text = FindCBIndex(ComboBox1, strSearchValue)
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("A1").CurrentRegion.Offset(1, 0).Value
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).Value
   ComboBox1.MatchEntry = fmMatchEntryNone 
End Sub

Public Function FindCBIndex(ByRef cbComboBox As ComboBox, ByRef strSearchValue As String) As Integer
    Dim n As Integer
    For n = 0 To cbComboBox.ListCount - 1
        If cbComboBox.List(n) = strSearchValue Then
            FindCBIndex = n
            Exit Function
        End If
    Next
    FindCBIndex = -1
End Function

SamD

 

[DanteAmor]

Hello

Can ayone help me to find original Combobox index Value even after i search. ?
in other words, when i search the list displays all the values, but clicking on the searched item it displays its index values as as per searched values.

What i want is when i search the value i want its original index number
A
1 Names
2 Smith *0
3 Jennifer *1
4 Jhon *2
5 Andrew *3
6 Bob *4
7 Celina *5
8 Sam *6
9 Catherine*7

in above range when clicked on combo i get index values from 0 to 7 repsectively.
suppose i type S the List displays Smith and Sam the index values of combobox i get is 0 and 1 rather than what is desired is to get 0 and 6
Kindly note that numbers 0 to 7 are index nos just for reference and same are not the values in Column B

Option Explicit
Private IsArrow As Boolean

Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).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


[COLOR=#ff0000]Private Sub ComboBox1_Click()

Dim idx As Long, strSearchValue As String
idx = ComboBox1.ListIndex
strSearchValue = ComboBox1.Text
 If idx <> -1 Then
        TextBox1.Value = Worksheets("Sheet1").Range("A" & idx + 2).Value
    End If
txtCbIndex.Text = FindCBIndex(ComboBox1, strSearchValue)
End Sub[/COLOR]

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("A1").CurrentRegion.Offset(1, 0).Value
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 0).Value
   ComboBox1.MatchEntry = fmMatchEntryNone 
End Sub

[COLOR=#ff0000]Public Function FindCBIndex(ByRef cbComboBox As ComboBox, ByRef strSearchValue As String) As Integer
    Dim n As Integer
    For n = 0 To cbComboBox.ListCount - 1
        If cbComboBox.List(n) = strSearchValue Then
            FindCBIndex = n
            Exit Function
        End If
    Next
    FindCBIndex = -1
End Function
[/COLOR]

SamD

Change the lines in red for the following:

Private Sub ComboBox1_Click()
    Dim f As Range
    If ComboBox1.ListIndex <> -1 Then
        Set f = Worksheets("Sheet1").Range("A:A").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

 

[SamDsouza]

Thanks DanteAmor. This was practical logic and with your suggestion I removed the below code which was tested and felt not required at all

[COLOR=#ff0000]Public Function FindCBIndex(ByRef cbComboBox As ComboBox, ByRef strSearchValue As String) As Integer
    Dim n As Integer
    For n = 0 To cbComboBox.ListCount - 1
        If cbComboBox.List(n) = strSearchValue Then
            FindCBIndex = n
            Exit Function
        End If
    Next
    FindCBIndex = -1
End Function
[/COLOR]

Samd

 

[DanteAmor]

Thanks DanteAmor. This was practical logic and with your suggestion I removed the below code which was tested and felt not required at all

[COLOR=#ff0000]Public Function FindCBIndex(ByRef cbComboBox As ComboBox, ByRef strSearchValue As String) As Integer
    Dim n As Integer
    For n = 0 To cbComboBox.ListCount - 1
        If cbComboBox.List(n) = strSearchValue Then
            FindCBIndex = n
            Exit Function
        End If
    Next
    FindCBIndex = -1
End Function
[/COLOR]

Samd

I'm glad to help you. Thanks for the feedback.