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.
Userform, listbox multiple selection and selection by pressing “Shift” and “Ctrl” keys have been added.
For listbox single select code :
For listbox multiple select code :
To select listbox items by pressing “Shift” and “Ctrl” keys :
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
The width of all columns of the SelectedData sheet is automatically adjusted according to the cell content.
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 .
As seen in the picture above , we used the following procedure to open the combobox without pressing the button(arrow on the right) .
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.
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