Multiselect ListBox for 2 column using Excel VBA

[liaaa]

Hello,
I want to make a listbox that contain 2 columns, ID and Item name. It is possible to choose multiple items in listbox with 2 column and transfer the data to the worksheet?
I also have a lot of items, so I think I must make a search bar for the listbox.
So first I search id on the search bar, select that item on listbox (the listbox must show 2 column), and then transfer the id and items to worksheet.


I have try to use combo box and listbox, but its not working.

Private Sub CmbITEM_Change()
Me.LbITEM.AddItem Me.CmbITEM
For x = 1 To 2
Me.LbITEM.List(LbITEM.ListCount - 1, x) = Me.CmbITEM.Column(x)
Next x
End Sub


Private Sub UserForm_Initialize()
CmbITEM.List = Sheet2.Range("A2:A1000").Value

Dim i As Long
For i = 1 To Apllication.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Me.CmbITEM.AddItem Sheet2.Cells(i, 1)
For x = 1 To 2
Me.CmbITEM.List(CmbITEM.ListCount - 1, x) = Sheet2.Cells(i, x + 1)
Next x
Next i

End Sub


Thank you for your help

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[liaaa]

Here is the the link

Monitoring BPP op.xlsm

Home 1

docs.google.com

 

[Akuini]

So first I search id on the search bar, select that item on listbox

1. Are you using the combobox below the the listbox as the search bar?
2. But the ID are unique, so why do you say "to choose multiple items"? I mean if, for example, you search for ID "1000005" then only item "Machine 2A" will be shown to select?

 

[liaaa]

1. Are you using the combobox below the the listbox as the search bar?
2. But the ID are unique, so why do you say "to choose multiple items"? I mean if, for example, you search for ID "1000005" then only item "Machine 2A" will be shown to select?

Yes i try to use combo box as search bar, and transfer the result to the listbox. Or you have another alternative to choose multiple rows directly in listbox and transfer the result to worksheet.

I want to use search bar because there are will be a lot of item and its impossible for me to scroll one by one. I also want to choose multiple rows. For example i search id 100005, then select the row result (id and item). Then i search for id 100001 and then select the result again. So by using search bar will help me to find the rows that i need faster.

 

[liaaa]

I'm sorry if I'm confusing you,
l try to make it like this video

But rather than scroll one by one and choose the item, I want to make the combo box searchable.
So in the combo box, I type the id, select the result, then the results will be transferred to the listbox, and all rows of results in the listbox will be transferred to the worksheet.
Thank you

 

[Akuini]

It is possible to choose multiple items in listbox with 2 column

1. You need only 2 columns (Id & Item) to appear in the listbox?
2. You want to search by Id only? or also by Item?

and transfer the data to the worksheet?

3. To what sheet?
4. About how many rows of data are there?

 

[liaaa]

1. You need only 2 columns (Id & Item) to appear in the listbox?
2. You want to search by Id only? or also by Item?

1. Yes only 2 columns
2. Search by the id only

3. To what sheet?
4. About how many rows of data are there?

I try to make multi user data entry, So when each person inputs data, the data will be transferred to a worksheet called database (name of the sheet is also '"database"). And every new input will be below the last row of data so the number of rows will continue to increase.

 

[liaaa]

1. You need only 2 columns (Id & Item) to appear in the listbox?
2. You want to search by Id only? or also by Item?

1. Yes only 2 columns
2. Search by the id only

3. To what sheet?
4. About how many rows of data are there?

I try to make multi user data entry, So when each person inputs data, the data will be transferred to a worksheet called database (name of the sheet is also 'database"). And every new input will be on the last row of data so the number of rows will continue to increase.

 

[Akuini]

So when each person inputs data, the data will be transferred to a worksheet called database (name of the sheet is also 'database").

There is no sheet called Database in your file that I've downloaded.