PDA

View Full Version : Get Range according multiple Listbox values



desantech
06-25-2018, 11:53 PM
Hello all

I need urgent help in this case:

1. I have multiple listboxes on a userform.
2. I have a database. 5 Different columns represnt a Category, like:
aaaaa 1 gggg 3 hhhhhh
aaaaa 1 gggg 3 hhhhhh
aaaaa 2 gggg 3 hhhhhh
aaaaa 2 hhhh 3 hhhhhh
aaaaa 3 hhhh 4 hhhhhh
aaaaa 3 hhhh 4 hhhhhh
aaaaa 3 hhhh 4 IIIIIIII
aaaaa 3 hhhh 4 JJJJJJJ

so if I select in 1.st listbox "aaaaa" then after checking how many represnt "aaaaa" I should get:

1 gggg 3 hhhhhh
1 gggg 3 hhhhhh
2 gggg 3 hhhhhh
2 hhhh 3 hhhhhh
3 hhhh 4 hhhhhh
3 hhhh 4 hhhhhh
3 hhhh 4 IIIIIIII
3 hhhh 4 JJJJJJJ

Then if I select "3" I should get:
hhhh 4 hhhhhh
hhhh 4 hhhhhh
hhhh 4 IIIIIIII
hhhh 4 JJJJJJJ

I got a great code from internet but I just cant "bind" the selected listbox rows to search further for the value I want:

I have this code for every listboxes (I just put 1+ to every name):

Private Sub ListBox4_Click()

Dim Kategorie4 As New Collection
Dim vItem111 As Variant
Dim rFound111 As Range
Dim FirstAddress111 As String

UserForm4.ListBox5.Clear

With Worksheets("Artikel").Range("AK2:A" & (Cells(1000, 1).End(xlUp).Row))
Set rFound111 = .Find(what:=ListBox4.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound111 Is Nothing Then
FirstAddress111 = rFound111.Address
On Error Resume Next
Do
Kategorie4.Add rFound111.Offset(, 1).Value, CStr(rFound111.Offset(, 1).Value)
Set rFound111 = .FindNext(rFound111)
Loop While rFound111.Address <> FirstAddress111
On Error GoTo 0
For Each vItem111 In Kategorie4
Me.ListBox5.AddItem vItem111
Next vItem111
End If
End With

End Sub

my aim is to get a search accordnig to listbox 1 selection and then to listbox 2 selection and then ... so on
I like this, just how to bind the search???

Its about 4000 rows data, trying to get some category

THANK YOU VERY MUCH FOR YOUR HELP

p45cal
06-26-2018, 01:06 AM
Supply a workbook with userform and sample (desensitised but realistic) data.

desantech
06-26-2018, 01:45 AM
Yes Sir

I just send you a small workbook, but if you open userform4 and play it in edit mode, then you see what I mean

For me important to have the same code for listbox1 listbox2 etc, but it shall take in concendarition the listbox value I click, like
you have 500 rows, but in the end you have only 5 according to all the selected one before


thanks again if you can help me

KR

desantech
06-26-2018, 01:58 AM
Anyone any idea? :crying::crying:

p45cal
06-26-2018, 02:46 AM
try:
Private Sub ListBox1_Click()

Dim Kategorie2 As New Collection
Dim vItem As Variant
Dim rFound As Range
Dim FirstAddress As String

UserForm4.ListBox2.Clear

With Worksheets("Artikel").Range("AG3:AG" & (Cells(1000, 1).End(xlUp).Row))
Set rFound = .Find(what:=ListBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound Is Nothing Then
FirstAddress = rFound.Address
On Error Resume Next
Do
Kategorie2.Add rFound.Offset(, 1).Value, CStr(rFound.Offset(, 1).Value)
Set rFound = .FindNext(rFound)
Loop While rFound.Address <> FirstAddress
On Error GoTo 0
For Each vItem In Kategorie2
Me.ListBox2.AddItem vItem
Next vItem
End If
End With

End Sub

Private Sub ListBox2_Click()

Dim Kategorie3 As New Collection
Dim vItem1 As Variant
Dim rFound1 As Range
Dim FirstAddress1 As String

UserForm4.ListBox3.Clear

With Worksheets("Artikel").Range("AH3:AH" & (Cells(1000, 1).End(xlUp).Row))
Set rFound1 = .Find(what:=ListBox2.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound1 Is Nothing Then
FirstAddress1 = rFound1.Address
On Error Resume Next
Do
Kategorie3.Add rFound1.Offset(, 1).Value, CStr(rFound1.Offset(, 1).Value)
Set rFound1 = .FindNext(rFound1)
Loop While rFound1.Address <> FirstAddress1
On Error GoTo 0
For Each vItem1 In Kategorie3
Me.ListBox3.AddItem vItem1
Next vItem1
End If
End With

End Sub

Private Sub ListBox3_Click()

Dim Marke As New Collection
Dim vItem11 As Variant
Dim rFound11 As Range
Dim FirstAddress11 As String

UserForm4.ListBox4.Clear

With Worksheets("Artikel").Range("AI3:AI" & (Cells(1000, 1).End(xlUp).Row))
Set rFound11 = .Find(what:=ListBox3.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound11 Is Nothing Then
FirstAddress11 = rFound11.Address
On Error Resume Next
Do
Marke.Add rFound11.Offset(, 1).Value, CStr(rFound11.Offset(, 1).Value)
Set rFound11 = .FindNext(rFound11)
Loop While rFound11.Address <> FirstAddress11
On Error GoTo 0
For Each vItem11 In Marke
Me.ListBox4.AddItem vItem11
Next vItem11
End If
End With

End Sub
Private Sub ListBox4_Click()

Dim Kategorie4 As New Collection
Dim vItem111 As Variant
Dim rFound111 As Range
Dim FirstAddress111 As String

UserForm4.ListBox5.Clear

With Worksheets("Artikel").Range("AJ3:AJ" & (Cells(1000, 1).End(xlUp).Row))
Set rFound111 = .Find(what:=ListBox4.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rFound111 Is Nothing Then
FirstAddress111 = rFound111.Address
On Error Resume Next
Do
Kategorie4.Add rFound111.Offset(, 1).Value, CStr(rFound111.Offset(, 1).Value)
Set rFound111 = .FindNext(rFound111)
Loop While rFound111.Address <> FirstAddress111
On Error GoTo 0
For Each vItem111 In Kategorie4
Me.ListBox5.AddItem vItem111
Next vItem111
End If
End With

End Sub

desantech
06-26-2018, 02:59 AM
oh man... what did you do???
this will also work for the next listboxes??
Dammit, thanks

desantech
06-26-2018, 03:35 AM
well, I just tried, still not good :(

still not indexing in listbox4 in relationship with listbox1-listbox2-listbox3 and so on

desantech
06-26-2018, 03:41 AM
If you try your code on this, you will see at the product "intermezzo", it indexes all others as well, not only what was chosen before

p45cal
06-26-2018, 12:22 PM
See attached.

desantech
06-26-2018, 02:14 PM
Hello and thank you trying to help me. I saw your workbook and I saw you made a sheet where the categories come from. Actuelly the idea would be that the categorizing is comming from the sheet articles (with a tons of rows), and listbox1 should be filled up from there as a starting point. Then if a user click on the listbox1 the excel search the next column according to what I have chosen in listbox1 and list the values it finds in listbox2. Then after cliccking on listbox2 it actually search the values that was selected in listbox1 and listbox2 and list them into listbox3 and so on... until I only get lets say some articles in the end that represent the vale in listbox1-listbox5. its kind of filtering. I have done it before, but would be much prefessional if it would be programetically.
So this file of you is actually not what I thought of :( but THANKS for replying

kind regards

p45cal
06-26-2018, 03:26 PM
All of what you said applies in the Workbook I attached.
All of the Values in the Sheet FormsList are dynamic. They change as you make choices in the listboxes and are transferred back to them. What's more, you can make your choices in the list boxes in any order you like.
The code was written to address your concerns in message no.8

p45cal
06-27-2018, 04:49 AM
Actuelly the idea would be that the categorizing is comming from the sheet articles (with a tons of rows), and listbox1 should be filled up from there as a starting point.It does, and from nowhere else.

desantech
06-27-2018, 06:51 AM
Hello Sir, I tried to get it work.

You will see the difference in my workbook and yours.
Please open mine and start the userform4
Then by selecting the categories it should react like that (programatically). Yours is different, its also awsome what you did (I copied your idea doing the categories on different sheets, but actually I wanted this kind of "extra job" be done by vba programming from the CORE sheet itself (Articles)).

As you see on my sheet artikelkat2 there are formulas that actually make the filter according to what is selected on the listboxes.

but if I do my filtering in this way, the data (and the volume of file) are huge... thats why I thought writing vba code could prevent this kind of solution

kind regards

desantech
06-27-2018, 07:01 AM
PS: just check the product "Intermezzo"
in each category I choose, different stuff come in... later on come color, dimension etc in other listboxes regarding to those values that where chosen before. In the end ou have only the articlenumers listed up where the user can choose
Its a very fast way finding articles if the articles have a bunch of attributes - my opinion

p45cal
06-28-2018, 06:18 AM
Its a very fast way finding articles if the articles have a bunch of attributes - my opinionYes, it is fast.
It is very similar to a few Excel built-in things such as AutoFilter, PivotTables and Advanced Filter; all fast.
It is the Advanced Filter that I have used to create lists.
In the attached, note:

There are only 2 sheets.
The sheet FormLists is empty except for Row 1, columns A:G and P:U where there are headers. There are no formulae (in the whole workbook).
The sheet FormLists should be hidden.
The sheet Artikel has has rows 1 and 2 swapped, to make a properly headed table.
There is a total of 77 lines of code.

The important lines of code are:
Sheets("Artikel").Range("A2:AV" & Sheets("Artikel").Cells(Rows.Count, 1).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("P1:U2"), CopyToRange:=.Range("A1:G1"), Unique:=False
which creates/updates a fitered list of your database in columns A:G of the FormLists sheet, and:

colm.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Empty, CopyToRange:=.Range("AA1").Offset(, ofset), Unique:=Truewhich creates/updates the lists which will go into the listboxes.

The important thing is that ALL the listboxes are updated with remaining possibilities regardless of which ListBox a selection is made in.
This means you can make selections from listboxes in any order. I have included a Clear button to reset the form.
Its a faster way finding articles if the articles have a bunch of attributes.
If you UserForm4.Show manually from the VBE when the FormLists sheet is the active sheet, and put a breakpoint(s) on, for example, the Private Sub UserForm_Initialize() line you will be able to step through the code with F8 on the keyboard and be able to see what goes on on the sheet behind the userform.


As you see on my sheet artikelkat2 there are formulas that actually make the filter according to what is selected on the listboxes.
but if I do my filtering in this way, the data (and the volume of file) are huge... thats why I thought writing vba code could prevent this kind of solutionThe method I've used is already light; you could make the file even lighter by clearing the FormLists sheet when you've finished with it with the likes of:
Sheets("FormLists").usedrange.offset(1).clear

desantech
06-28-2018, 02:36 PM
I thank you very much for your help. I will check out your solution later this week. Kind regards