Excel VBA Advanced Filter Multiple Comboboxes Userform Listbox - How to?

RPM0410A

New Member
Joined
May 25, 2019
Messages
1
Hi

I'm a first time poster so apologies in advance if any of the below is not allowed or does not make sense etc. I'm more than happy to amend it/clarify anything.

I have a userform which allows me to view values in worksheet1 using a listbox. I have several textboxes on the userform which show the corresponding value from the listbox when selected. I am able to use these textboxes to amend or add data which in turn updates the worksheet and listbox when a command button is pressed. I've managed to get this working perfectly through researching and manipulating code found on various forums including this one.

However, what I have been trying to workout/research for weeks (and so far failed to do) is how to use comboboxes on the userform, to filter the data in worksheet1, and display it in listbox1 so it can be edited or just viewed. For the purpose of this part of what I am trying to achive, my setup is as follows:

Worksheet1
Column A = Name
Column B = Age
Column C = Location
Column D = Date Sent
Column E = Replied
Column F = Cost

EXAMPLE Worksheet1 Values for rows 1-7 and columns A-F
1 Name Age Location Date Sent Replied Cost
2 Adam 22 UK 01/05/2019 Yes £200
3 Ben 34 UK
4 Carl 45 UK
5 Carl 34 USA 01/05/2019 No
6 Ben 22 Japan 02/05/2019 Yes £300
7 Adam 37 France 10/05/2019 No

Userform1
Listbox1
Combobox1 = Name
Combobox2 = Age
Combobox3 = Location
Combobox4 = Date Sent
Combobox5 = Replied
Combobox6 = Cost


Is it possible to:
Q1. Filter the data in worksheet1, based on the selections made in any of the 6 comboboxes, and have it displayed in listbox1?
This would include selecting a value in any of the comboboxes and having the remaining comboboxes automatically populate with possible options only. This would need to be able to be applied to any such combination eg starting to filter on combobox5
EXAMPLE:
If Name selected in combobox1 were to be Adam then the remaining comboboxes would populate as follows:
Combobox2 = 22, 37
Combobox3 = UK, France
Combobox4 = 01/05/2019, 10/06/2019
Combobox5 = Yes, No
Combobox6 = £200

Listbox1/Worksheet1 View
1 Name Age Location Date Sent Replied Cost
2 Adam 22 UK 01/05/2019 Yes £200
7 Adam 37 France 10/05/2019 No


Q2. Allow the additional comboboxes to be used to narrow the list further
Again this would need to be able to be applied to any such combination including comboboxes not being used
EXAMPLE:
Combobox1 = Adam
Combobox2 = No Selection Made
Combobox3 = France
Combobox4 = No Selection Made
Combobox5 = No
Combobox6 = No Selection Made

Listbox1 and Worksheet1 view:
Name Age Location Date Sent Replied Cost
7 Adam 37 France 10/05/2019 No


Q3. List only the unique values of possible options within the combobox list
So combobox1 would populate with
Adam
Ben
Carl

Not
Adam
Ben
Carl
Carl
Ben
Adam



Q4. List the options A-Z in each combobox at all times


I don't want to use additional sheets as I'd like the data to be filtered and amended in its current sheet so that the rows can be amended if needs be.

Any help/guidance received I will be very, very grateful for!

Many Thanks in Advance!

RPM
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

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
Top