Excel Search In Data Validation List (Searchable Drop Down List)

Excel Userform - Address Book   

I updated the userform that I did previously to run it in all version of Excel.

With this VBA userform ;

☑️ Data can be added

☑️ Data can be deleted

☑️ Data can be edited

☑️ Data can be searched on the sheet

☑️ Listbox can be filled and emptied with button.

☑️ Listbox can be scrolled with the spin button.

         Id numbers are generated automatically  when new record is added to the worksheet and  when record is removed.

The listbox is empty when the userform is opened.

The entered value in the searching box can be searched on the worksheet. Listbox are filled with the found results .

         

VBA Codes of “Save” button : 

Private Sub CommandButton1_Click()
Dim new_reg As Long
If TextBox1.Text = Empty Or TextBox3.Text = Empty Then
MsgBox "Incomplete Data", vbCritical, ""
TextBox1.SetFocus
Exit Sub
End If

For new_reg = 2 To Cells(Rows.Count, 2).End(xlUp).Row  'It is checked whether the record already exists, if there is the record, a warning is given.
If Cells(new_reg, "B") = TextBox1 Then
MsgBox "This name is already registered !", vbInformation, ""
TextBox1 = Empty
Exit Sub: End If: Next

sonsat = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(sonsat, 1) = sonsat - 1
Cells(sonsat, 2) = TextBox1
Cells(sonsat, 3) = TextBox2
Cells(sonsat, 4) = TextBox3
Cells(sonsat, 5) = TextBox4
Cells(sonsat, 6) = TextBox5
Cells(sonsat, 7) = TextBox6
Cells(sonsat, 8) = TextBox7
Cells(sonsat, 9) = TextBox8
Range("A" & sonsat & ":I" & sonsat).Font.ColorIndex = 11  'The font color of the record is set.

MsgBox "Registration is successful", vbInformation
Range("A" & sonsat & ":I" & sonsat).Interior.ColorIndex = 25 'The background color of the record is set.
Call sort_id
Call text_boxes_clear
End Sub

The listbox column widths are automatically adjusted according to the widths of the sheet’s column using VBA codes :

Private Sub CommandButton7_Click() 'Fill All Records To Listbox button
sat = Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.List = Sheets("Main").Range("B2:I" & sat).Value
With ListBox1
        For i = 1 To 8
            deg = deg & CLng(Columns(i + 1).Width) & ";"
        Next i
        .ColumnWidths = deg
End With
ListBox1.ColumnCount = 8
...

     ➡️ Alternative Link