Excel Advanced Userform Sample

 Excel VBA Advanced UserForm Tutorial (Full Sample with ListBox & Search Features)

      The benefits of using the userform are indisputable in order to enter data into the worksheet and analyze the data on worksheet more easily and to make changes on the data.

This advanced Excel VBA UserForm is not limited to data display only. It also allows full data management directly from the UserForm interface.

With this form, users can:

➩ Add new records to the worksheet
➩ Edit existing records using the UserForm fields

➩ Search across multiple columns
➩ Delete selected records safely
➩ Automatically sync the selected ListBox item with the worksheet row

When a record is selected in the ListBox, the corresponding row in the worksheet is also selected. This ensures data consistency and gives users visual confirmation of the active record.
These features make the UserForm suitable for real-life CRUD operations (Create, Read, Update, Delete).

---

✓ The change that made on the record can be visibled immediately in the listbox and worksheet. ⬇

...
Set bul = Sheets("Data").Range("A2:A" & lastrow).Find(What:=ListBox1, Lookat:=xlWhole)
With Sheets("Data")
.Cells(bul.Row, 1) = TextBox1
.Cells(bul.Row, 2) = TextBox2
.Cells(bul.Row, 3) = TextBox3
.Cells(bul.Row, 4) = TextBox4
.Cells(bul.Row, 5) = TextBox5
.Cells(bul.Row, 6) = TextBox6
.Cells(bul.Row, 7) = TextBox7
.Cells(bul.Row, 8) = TextBox8
.Cells(bul.Row, 9) = TextBox9
.Cells(bul.Row, 10) = TextBox10
.Cells(bul.Row, 11) = TextBox11
.Cells(bul.Row, 12) = TextBox12
.Cells(bul.Row, 13) = TextBox16
.Cells(bul.Row, 14) = TextBox17
.Cells(bul.Row, 15) = TextBox18
End With
If Not sheet_exists("FilteredData") Then
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
ListBox1.Value = Sheets("Data").Cells(bul.Row, 1)
End If
...

---

✓ With buttons can be navigated between the listbox items and the cells on the sheet . When the index number is entered in the textbox (TextBox15) in the middle and the “Enter key” is pressed, the item with the index number is displayed at the top of the listbox (ListBox1.TopIndex) and on the worksheet.

Private Sub TextBox15_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 13 Then Exit Sub
On Error Resume Next
If IsNumeric(TextBox15.Value) Then
If TextBox15 <> Empty Then
ListBox1.ListIndex = (TextBox15.Value) - 2
ListBox1.TopIndex = (TextBox15.Value) - 2
End If
End If
End Sub

---

✓ Userform, listbox multiple selection and selection by pressing Shift and Ctrl keys have been added.

For listbox single select code : ListBox1.MultiSelect = 0

For listbox multiple select code : ListBox1.MultiSelect = 1

To select listbox items by pressing Shift and Ctrl keys : ListBox1.MultiSelect = 1

We have used a checkbox to select all items of the listbox and deselect items. ⬇

Private Sub CheckBox1_Click()
Dim r As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ListBox1.ListIndex = -1
If CheckBox1.Value = True Then
ListBox1.MultiSelect = fmMultiSelectMulti
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = True
Next r
Else
ListBox1.MultiSelect = fmMultiSelectSingle
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = False
Next r
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

✓ The item or items selected from the listbox or all listbox items can be copied to the “SelectedData” sheet by pressing to the button.
To copy from listbox to sheet : ⬇

Dim Litem, LbRows, LbCols As Long
 Dim bu As Boolean
 Dim Lbloop, Lbcopy As Long
  
 LbRows = ListBox1.ListCount - 1
 LbCols = ListBox1.ColumnCount - 1
   
For Litem = 0 To LbRows
If ListBox1.Selected(Litem) = True Then
    bu = True
Exit For
  End If
Next

If Not Sheet_Exists_Cntrl("SelectedData") Then
Call New_Sheet2
End If

    If bu = True Then
With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    For Litem = 0 To LbRows
    If ListBox1.Selected(Litem) = True Then 'Row selected
                  'Increment variable for row transfer range
    Lbcopy = Lbcopy + 1
    For Lbloop = 0 To LbCols
    .Cells(Lbcopy, Lbloop + 1) = ListBox1.List(Litem, Lbloop)
    Next Lbloop
    End If
    Next
    For m = 0 To LbCols
With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(0, m).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = 23
End With
    Next
End With
     Else
     MsgBox "Nothing chosen", vbCritical
    Exit Sub
    End If
MsgBox "The Selected Data Are Copied.", vbInformation
Sheets("SelectedData").Select
Sheets("SelectedData").Columns.AutoFit

Sheets(“SelectedData”).Columns.AutoFit  → The width of all columns of the SelectedData sheet is automatically adjusted according to the cell content.

If Not Sheet_Exists_Cntrl("SelectedData") Then
Call New_Sheet2
End If                    → With these codes, SelectedData sheet is created if it does not exist in the workbook.

VBA codes to check if a worksheet exists (We added the user defined function to Module1.) : ⬇

Function Sheet_Exists_Cntrl(SheetName As String) As Boolean
    Dim pg As Excel.Worksheet
    On Error GoTo eHandle
    Set pg = ThisWorkbook.Worksheets(SheetName)
    Sheet_Exists_Cntrl = True

    Exit Function
eHandle:
    Sheet_Exists_Cntrl = False
End Function

If there is no “SelectedData” sheet, the procedure to create it is as follows : ⬇

Sub New_Sheet2()
    If Not Sheet_Exists_Cntrl("SelectedData") Then
    ThisWorkbook.Sheets.Add( _
    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "SelectedData"
    End If
End Sub

---

✓ We added the textbox(TextBox13) on the userform to search in the sheet. The user can select the column that want to search from the drop-down list. We selected 4 columns to search data. When the userform opened ,To list these column headers in the drop-down list ,we added the following codes to the Userform_Initialize method . ⬇

With ComboBox1
.AddItem Sheets("Data").Range("A1").Value
.AddItem Sheets("Data").Range("B1").Value
.AddItem Sheets("Data").Range("D1").Value
.AddItem Sheets("Data").Range("L1").Value
End With 

📌 We used the following procedure to open the combobox without pressing the button(arrow on the right) . ⬇

Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
Me.ComboBox1.DropDown
End Sub

---

✓ We used the AutoFilter method to search data faster in the worksheet and display results in the listbox. The filtered data by AutoFilter method is first copied to FilteredData sheet -if this sheet there isn’t in the workbook, it is created and hidden by the function-. The copied data to the FilteredData sheet is listed using Listbox List method on the userform. ⬇

ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value

---

✓ We added a progress bar on the userform. Userform progress bar informs to the user whether the operation (the ran macro) has ended or not.
The UserForm also includes a progress bar to indicate long-running operations such as:
➩ Loading large datasets
➩ Processing multiple records
➩ Performing bulk updates

Using a progress bar improves user experience by:
➩ Preventing the impression that Excel has frozen
➩ Giving visual feedback during operations
➩ Making the application feel more professional

Progress indicators are especially important in advanced Excel VBA applications.

The Main () procedure that we created is called to run the Progress bar. ⬇

Sub Main()
Dim i, topl As Integer
topl = 5000
For i = 1 To topl
If i Mod 5 = 0 Then
ProgressBar i / topl
End If
Next i
lblDone.Width = 0
lblPct.Visible = False
End Sub

Sub ProgressBar(PctrDone As Single)
    lblDone.Width = PctrDone * (lblRemain.Width - 2)
    lblPct.Visible = True
    lblPct.Caption = Format(PctrDone, "0%")
    DoEvents
End Sub

1. Can I add, edit, and delete records using this Excel UserForm? +

Yes. The UserForm supports full CRUD operations, allowing users to add new records, edit existing data, and delete selected entries directly from the form.

2. Can I search records by Name, Company, City, or Estimated Revenue? +

Yes. The UserForm allows searching across multiple columns such as Name, Company, City, and Estimated Revenue. Matching results are dynamically displayed in the ListBox.

3. Does the ListBox selection synchronize with the worksheet? +

Yes. When an item is selected in the ListBox, the corresponding row in the worksheet is automatically selected, ensuring data consistency.

4. Does the UserForm include a progress bar? +

Yes. The UserForm includes a progress bar to provide visual feedback during long operations such as loading data or processing multiple records.

5. Is this UserForm suitable for real-world Excel projects? +

Absolutely. This UserForm is designed for real-world applications such as personnel management, CRM-like lists, inventory tracking, and financial data management.

Was this Excel VBA article helpful?

0 readers found this helpful