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
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