Excel Advanced Userform Sample

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

📥 Sample workbook can be downloaded here