The benefits of using the userform are indisputable in order to enter data into the worksheet and analyze the data on the page more easily and to make changes on the data.
For this purpose, we designed a userform with advanced features. Unlike other userforms -as well as record adding, deleting and editing features-, we have added features such as listbox multiple selection feature, advanced search feature, textbox that shows the index sequence number . The item or items selected on the listbox can be copied to the other sheet.
We also made changes to the userform record editing feature. 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 = 2
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

As seen in the picture above , 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 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