Combo Box Typing and Auto Completion in Number

[nandhavnk]

Hi,

I have coded wages report with ID No selection in combo box.

What I expecting is When I typing 1 in combo box, display 1 related numbers and then click tab button related name in textbox.

Here these changes happening in scrolling or mouse clicking options only.

Can experts guide me to sorting out this.

Here Code is,

Private Sub cbanoO_change()
  Dim f As Range
  txtdelO.Value = ""
  txtdateO.Value = ""
  'Now go through and check the values of the first column against what was selected in the combo box.
  If cbanoO.ListIndex > -1 Then
    Set f = Sheets("Sheet1").Range("A:A").Find(CDbl(cbanoO.Value), , xlValues, xlWhole)
    If Not f Is Nothing Then
      txtdelO.Value = Sheets("Sheet1").Range("D" & f.row).Value
    End If
  End If
  'sorting in ascending
  With Me.cbanoO
    For X = LBound(.List) To UBound(.List)
        For Y = X To UBound(.List)
            If .List(Y, 0) + 0 < .List(X, 0) + 0 Then
                blah = .List(Y, 0)
                .List(Y, 0) = .List(X, 0)
                .List(X, 0) = blah
            End If
        Next Y
    Next X
  End With
End Sub

Private Sub UserForm_Activate()
  Dim dic As Object
  Dim i As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet1")
    For i = 3 To .Range("A" & Rows.Count).End(3).row
      dic(.Range("A" & i).Value) = Empty
    Next
  End With
cbanoO.List = dic.keys
txtdelO.Enabled = False
End Sub

Combo Box - Auto Selection with Number.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Monthly Wages List
2	ID No	Date	Duration	Name	Wages
3	1	1-Nov-22	8:00	Araminta	1000.00
4	2	1-Nov-22	7:00	Arden	875.00
5	3	1-Nov-22	5:00	Azalea	625.00
6	4	1-Nov-22	6:00	Birch	750.00
7	5	1-Nov-22	6:30	Birdie	812.50
8	6	1-Nov-22	7:30	Blythe	937.50
9	7	1-Nov-22	7:45	Booker	968.75
10	8	1-Nov-22	8:00	Catherine	1000.00
11	9	1-Nov-22	3:00	Clover	375.00
12	10	1-Nov-22	5:00	Dane	625.00
13	11	1-Nov-22	5:30	Garrison	687.50
14	12	1-Nov-22	3:30	Hale	437.50
15	13	1-Nov-22	6:30	Jump	812.50
16	14	1-Nov-22	7:30	Kit	937.50
17	15	1-Nov-22	7:45	Lavender	968.75
18	16	1-Nov-22	1:45	Lilac	218.75
19	17	1-Nov-22	2:00	Mary	250.00
20	18	1-Nov-22	5:15	Micheal	656.25
21	19	1-Nov-22	7:15	Nick	906.25
22	20	1-Nov-22	1:30	Oberon	187.50
23	21	1-Nov-22	8:00	Peter	1000.00
24	22	1-Nov-22	5:00	Posey	625.00
25	23	1-Nov-22	2:00	Shaw	250.00
26	24	1-Nov-22	6:30	Tobin	812.50
27	25	1-Nov-22	8:00	Waverly	1000.00
28	26	1-Nov-22	8:00	Winston	1000.00
29	10	2-Nov-22	2:00	Dane	250.00
30	9	2-Nov-22	8:00	Clover	1000.00
31	13	2-Nov-22	5:00	Jump	625.00
Sheet1

Cell Formulas
Range	Formula
C3,C30,C27:C28,C23,C10	C3	=8/24
C4	C4	=7/24
C5,C31,C24,C12	C5	=5/24
C6	C6	=6/24
C7,C26,C15	C7	=6.5/24
C8,C16	C8	=7.5/24
C9,C17	C9	=7.75/24
C11	C11	=3/24
C13	C13	=5.5/24
C14	C14	=3.5/24
C18	C18	=1.75/24
C19,C29,C25	C19	=2/24
C20	C20	=5.25/24
C21	C21	=7.25/24
C22	C22	=1.5/24
E3:E31	E3	=(C3*24)*125

UserForm Screenshot is,

Thanks

 

[DanteAmor]

Check if the following works for you.
If you want to display any captured number use this:

If ky Like "*" & dato & "*" Then

If you want only what starts with the captured number use this:

If ky Like dato & "*" Then

I recommend you sort the data in the sheet before opening the userform. When you close the userform, order the data again as you want.

Change all your code to this:

Option Explicit

Dim a As Variant
Dim cargando As Boolean
Dim dic As Object

Private Sub cbanoO_change()
  Dim dato As String
  Dim ky As Variant
  
  If cargando = True Then Exit Sub
  txtdelO.Value = ""
  txtdateO.Value = ""
  cargando = True
  dato = cbanoO.Value
  cbanoO.Clear
  For Each ky In dic.keys
    If ky Like "*" & dato & "*" Then
      cbanoO.AddItem ky
    End If
  Next
  cbanoO.Value = dato
  '
  If cbanoO.ListIndex > -1 Then
    txtdelO.Value = a(dic(Val(cbanoO.Value)), 4)
  End If
  txtdateO.SetFocus
  cbanoO.SetFocus
  cbanoO.DropDown
  cargando = False
End Sub

Private Sub UserForm_Activate()
  Dim i As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet1")
    a = .Range("A3:E" & .Range("A" & Rows.Count).End(3).Row).Value
    For i = 1 To UBound(a, 1)
      dic(a(i, 1)) = i
    Next
  End With
  cbanoO.List = dic.keys
  cbanoO.MatchEntry = fmMatchEntryNone
  txtdelO.Enabled = False
End Sub

 

[nandhavnk]

@DanteAmor

Sorry for late reply.

I applied these codes & working clearly.

Thanks for your guidance.