Kapat

The Updated Excel Userform

excel  listbox context menuExcel Userform Example

With this multi-functional userform ;
✔️ New data can be saved in the worksheet.
✔️ Data can be updated,changed.
✔️ Data can be deleted.
✔️ With textbox, data can be searched in the listbox based on the selected column from combobox.
✔️ Listbox items can be sorted alphabetically as ascending(A-Z) or descending(Z-A).
✔️ Progress Bar Feature.
✔️ Next ,Previous ,First ,Last Recording Buttons.
✔️ The listbox can be scrolled with spin buttons up and down.

excel multi functional userform

✔️ In this userform, the filtering is performed on the listbox according to the entered value in the textbox. Results are listed on the listbox.

The VBA codes that are triggered when “Search” button is pressed to search a value the listbox columns : excel vba search in listbox column

✔️ Listbox items can be sorted alphabetically from A to Z according to the names in the 1st column. 

Private Sub CommandButton5_Click() 'Search Button
Dim sat, s As Long
Dim deg1, deg2 As String
Sheets("Data").Activate
Application.ScreenUpdating = False
If TextBox13.Value = "" Then
MsgBox "Please enter a value", vbExclamation
TextBox13.SetFocus
Exit Sub
End If
If ComboBox1.Value = "" Or ComboBox1.Value = "-" Then
MsgBox "Choose a Filter Field", vbExclamation, ""
ComboBox1.SetFocus
Exit Sub
End If
For a = 1 To 12         'Clear textboxes(1-12)
Controls("textbox" & a) = ""
Next
With ListBox1
.Clear
.ColumnCount = 12
.ColumnWidths = "92;140;110;65;65;35;40;65;65;115;150;65"
End With
Call Main               'Progress Bar

deg2 = TextBox13.Value
Select Case ComboBox1.Value
Case "Name"
For sat = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set deg1 = Cells(sat, "A")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Case "Company"
For sat = 2 To Cells(Rows.Count, 2).End(xlUp).Row
Set deg1 = Cells(sat, "B")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Case "City"
For sat = 2 To Cells(Rows.Count, 4).End(xlUp).Row
Set deg1 = Cells(sat, "D")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Case "Estimated Revenue"
For sat = 2 To Cells(Rows.Count, 12).End(xlUp).Row
Set deg1 = Cells(sat, "L")
If deg1 Like deg2 & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next
End Select
Application.ScreenUpdating = True
Label15.Caption = ListBox1.ListCount
End Sub

We added the following codes into Module to sort listbox columns alphabetically : excel sort listbox items alfhabetically

Sub Sort_ListBox(oListBx As MSForms.ListBox, sColmn As Integer, sType As Integer, sDir As Integer)
 Dim var_item, var_temp As Variant
 Dim i, j As Long
 Dim c As Integer
 
 'Put the items in a variant array
 var_item = oListBx.List
 Application.EnableEvents = False
 'Sort the Array as Alphabetically 1
 If sType = 1 Then
 For i = LBound(var_item, 1) To UBound(var_item, 1) - 1
 For j = i + 1 To UBound(var_item, 1)
 'Sort Ascending 1
 If sDir = 1 Then
 If var_item(i, sColmn) > var_item(j, sColmn) Then
 For c = 0 To oListBx.ColumnCount - 1 'Allows sorting of multi column List Boxes
 var_temp = var_item(i, c)
 var_item(i, c) = var_item(j, c)
 var_item(j, c) = var_temp
 Next c
 End If
 Application.EnableEvents = True
 'Sort Descending 2
 ElseIf sDir = 2 Then
 If var_item(i, sColmn) < var_item(j, sColmn) Then
 For c = 0 To oListBx.ColumnCount - 1 'Allows sorting of multi column List Boxes
 var_temp = var_item(i, c)
 var_item(i, c) = var_item(j, c)
 var_item(j, c) = var_temp
 Next c
 End If
 End If
 
 Next j
 Next i
 'Sort the Array as Numerically 2
 'Change CInt with another type (CLng, CDec, etc.) depending on type of numbers in the column
 ElseIf sType = 2 Then
 For i = LBound(var_item, 1) To UBound(var_item, 1) - 1
 For j = i + 1 To UBound(var_item, 1)
 'Sort Ascending (1)
 If sDir = 1 Then
 If CInt(var_item(i, sColmn)) > CInt(var_item(j, sColmn)) Then
 For c = 0 To oListBx.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
 var_temp = var_item(i, c)
 var_item(i, c) = var_item(j, c)
 var_item(j, c) = var_temp
 Next c
 End If
 'Sort Descending (2)
 ElseIf sDir = 2 Then
 If CInt(var_item(i, sColmn)) < CInt(var_item(j, sColmn)) Then
 For c = 0 To oListBx.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
 var_temp = var_item(i, c)
 var_item(i, c) = var_item(j, c)
 var_item(j, c) = var_temp
 Next c
 End If
 End If
 
 Next j
 Next i
 End If
'Set the list to the array
 oListBx.List = var_item
End Sub

We can run by calling Sort_ListBox procedure like this with the “sorting” procedure that triggered when the Sort button is clicked :
Sub sorting()
Run "Sort_ListBox", UserForm1.ListBox1, 0, 1, 1
End Sub

With the above codes, listbox 1st column is sorted alphabetically in ascending order.
➡️ For to sort listbox 2nd column alphabetically in ascending order :
Run "Sort_ListBox", UserForm1.ListBox1, 1, 1, 1
➡️ For to sort listbox 2nd column alphabetically in descending order :
Run "Sort_ListBox", UserForm1.ListBox1, 1, 1, 2
➡️ For to sort listbox 2nd column numerically in ascending order :
Run "Sort_ListBox", UserForm1.ListBox1, 1, 2, 1

📥 Download workbook that contained userform sample