Kapat

Show New Userform When Userform Listbox Item Is Clicked

excel  vba sample Display Second Userform
By Clicking Listbox

Firstly, in this example ; we have listed the cells of “Data” worksheet, which consists of Ship Name, Ship Via, Ship Address, Ship City, Ship Region, Ship Postal Code, Ship Country, Freight, Date columns, in the userform.

excel populate userform listbox

We filled the userform listbox with the following VBA codes : excel populate listbox

Private Sub UserForm_Initialize()
Dim say As Long
Sheets("Data").Activate
ActiveSheet.Range("A:I").SpecialCells(xlCellTypeBlanks).Rows.Delete xlUp

say = WorksheetFunction.CountA(Worksheets("Data").Range("A:A"))
      ListBox1.RowSource = "Data!A2:I" & say
      ListBox1.ColumnCount = 9
      ListBox1.ColumnWidths = "120;35;60;60;60;60;60;60;60"
End Sub

When we examine the codes above; ➡️ With this code snippet
ActiveSheet.Range("A:I").SpecialCells(xlCellTypeBlanks).Rows.Delete xlUp
the activesheet is checked based on column A, if there is an empty row, it is deleted.
➡️ With this code snippet
say=WorksheetFunction.CountA(Worksheets("Data").Range("A:A"))
We find the number of non-empty cells in column A, assign the result to “say” variable. COUNTA function is used as worksheet function (as it is a item of WorksheetFunction family in VBA) to count the non-empty cells through a certain sheet range.

To search a value in listbox columns, there is a textbox and button control at the top of each column. When a value (letter etc.) is entered in the textbox and the button below it is pressed, the listbox content is cleared and the search results are listed on listbox. excel search in listbox column

excel vba search in listbox column

For example ; VBA codes that are triggered when the button is pressed to search in the listbox column 1 are as follows : excel codes of search in listbox

Private Sub cmdbul1_Click()
Application.ScreenUpdating = False
ListBox1.RowSource = Empty
For Each isim In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If UCase(LCase(isim)) Like UCase(LCase(TextBox1)) & "*" Then
        liste = ListBox1.ListCount
            ListBox1.AddItem
            ListBox1.List(liste, 0) = isim
            ListBox1.List(liste, 1) = isim.Offset(0, 1)
            ListBox1.List(liste, 2) = isim.Offset(0, 2)
            ListBox1.List(liste, 3) = isim.Offset(0, 3)
            ListBox1.List(liste, 4) = isim.Offset(0, 4)
            ListBox1.List(liste, 5) = isim.Offset(0, 5)
            ListBox1.List(liste, 6) = isim.Offset(0, 6)
            ListBox1.List(liste, 7) = VBA.Format(isim.Offset(0, 7), "#,##.00")
            ListBox1.List(liste, 8) = VBA.Format(isim.Offset(0, 8), "dd.mm.yyyy")
                       
     End If
Next
Application.ScreenUpdating = True
End Sub

When any item of listbox1 is double-clicked, other userform is viewed. The loaded this userform’s text boxes are populated based on listbox clicked item value . In this way, the item of listbox1 can be examined more clearly in addition to the item can be changed or deleted. excel vba load userform

excel when listbox clicked load new userform

VBA codes that supplied the loading of the userform and the filling of the text boxes when double-clicking on the listbox1 item : excel vba show userform

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Load UserForm2
UserForm2.TextBox1 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 0)
UserForm2.TextBox2 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 1)
UserForm2.TextBox3 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 2)
UserForm2.TextBox4 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 3)
UserForm2.TextBox5 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 4)
UserForm2.TextBox6 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 5)
UserForm2.TextBox7 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 6)
UserForm2.TextBox8 = VBA.Format(UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 7), "#,##.00")
UserForm2.TextBox9 = VBA.Format(UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 8), "dd.mm.yyyy")
UserForm2.TextBox10 = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, 0)
UserForm2.Show
End Sub

📥 Download sample workbook