Filter on a Listbox with multiple select items

Romera

New Member
Joined
May 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all!

I am stuck with the following problem:

I created a listbox in a form with the option to select multiple items. When hitting the 'Add' button, the selected items are stored in one column in a table separated by a semi-column. However, I would like to filter a Pivottable based on the selected items uniquely.

This is the form I created in VBA (the content is a bit different, but this is the idea):
1620850859008.png

The problem is that all the items are stored in one column, so I can filter on e.g. "A, B, D" and "B, C, E" instead of "A", "B", "C", "D" or "E" individually.

Is there a way to do so? I tried using PowerQuery to split the columns but this did not work for me either.

I hope someone can help me out, would appreciate every little help :)


All the best,
Romera
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi Romera,

If you store value in individual rows based on listbox selected values instead of storing in a single row then it can resolve the issue.


Thanks,
Saurabh
 
Upvote 0
Hi Romera,

If you store value in individual rows based on listbox selected values instead of storing in a single row then it can resolve the issue.


Thanks,
Saurabh

Hi Saurabh,

Can you help me in how to do that? I do not know where to start with this.

Thanks in advance!

Romera
 
Upvote 0
Hi Romera,

Please check if below code helps you.

VBA Code:
Private Sub cmdAdd_Click()
Dim blankRow As Integer, listitem As Integer, rowno As Integer

'To find blank row available in active sheet.
blankRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

rowno = blankRow + 1

'To check selected items in List box
For listitem = 0 To lstRedFlag.ListCount - 1
    If lstRedFlag.Selected(listitem) Then
        With Worksheets("Product")
            .Cells(rowno, 1) = txtNum
            .Cells(rowno, 2) = txtName
            .Cells(rowno, 3) = lstRedFlag.List(listitem)
            rowno = rowno + 1
        End With
    End If
Next

End Sub
 

Attachments

  • addItemList.JPG
    addItemList.JPG
    108.5 KB · Views: 22
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top