Add Multiple Columns to the List box based on Data Selected in COMBOBOX
Public SH As Worksheet
Function DefineWorksheet()
Set SH = ThisWorkbook.Sheets("Sheet2")
End Function
'Insert the Items into Combo Box
Private Sub UserForm_Initialize()
Me.ComboBox1.Clear
DefineWorksheet
LastRow = SH.Range("B" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow
If WorksheetFunction.CountIf(Range("B2:B" & r), Range("B" & r)) = 1 Then
Me.ComboBox1.AddItem SH.Range("B" & r).Value
End If
Next
End Sub
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
Me.ListBox1.Font.Size = 14
Me.ListBox1.Font.Name = "Calibri"
Me.ListBox1.ColumnCount = 5
DefineWorksheet
LastRow = SH.Range("B" & Rows.Count).End(xlUp).Row
With Me.ListBox1
.AddItem
.List(0, 0) = SH.Cells(1, 1).Value
.List(0, 1) = SH.Cells(1, 2).Value
.List(0, 2) = SH.Cells(1, 3).Value
.List(0, 3) = SH.Cells(1, 4).Value
.List(0, 4) = SH.Cells(1, 5).Value
End With
ListBoxRow = 1
For r = 2 To LastRow
If SH.Range("B" & r).Value = Me.ComboBox1.Value Then
With Me.ListBox1
.AddItem
.List(ListBoxRow, 0) = SH.Cells(r, 1).Value
.List(ListBoxRow, 1) = SH.Cells(r, 2).Value
.List(ListBoxRow, 2) = SH.Cells(r, 3).Value
.List(ListBoxRow, 3) = SH.Cells(r, 4).Value
.List(ListBoxRow, 4) = SH.Cells(r, 5).Value
ListBoxRow = ListBoxRow + 1
End With
End If
Next
Me.ListBox1.ListStyle = fmListStyleOption
Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub
Private Sub CommandButton1_Click()
r = 1
Set SH = ThisWorkbook.Sheets.Add(after:=Worksheets(Sheets.Count))
SH.Activate
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
SH.Cells(r, 1).Value = Me.ListBox1.List(i, 0)
SH.Cells(r, 2).Value = Me.ListBox1.List(i, 1)
SH.Cells(r, 3).Value = Me.ListBox1.List(i, 2)
SH.Cells(r, 4).Value = Me.ListBox1.List(i, 3)
SH.Cells(r, 5).Value = Me.ListBox1.List(i, 4)
r = r + 1
End If
Next
Unload UserForm1
End Sub