Dependent Comboboxes in Userform using a filter

[akim]

Hi everyone,
this is my first post, so apologies if I make any mistake in posting here. I have been browsing through the forum and found some similar threads but none that really matched my problematic.
I am currently managing the stock and dispatches for the company I work for.

In my inventory sheet I have 3 columns :
- product name
- batch number
- quantity available for each batch

I created a little tailor made search engine with two dropdown lists on the spreadsheet to prepare my dispatches.
Step 1 : you select the product ; only products that are in stock show up in the list (those that have a balance of 0 do not appear)
Step 2 : you select the batch based on the previously selected product ; only batches that are in stock show up in the list (those that have a balance = 0 do not appear)

Here are two pictures to illustrate : first I select the product, then I select the batch. notice that values that have 0 kg in stock do not show up in any of the lists.

In order to automate the process of creating a dispatch and make it more user-friendly, I want to transcribe this process into a Userform in VBA with ComboBoxes.
At this point I have managed to show the available products in CboMaterial, however I am hitting a wall when it comes to create the dependent dropdown list with the corresponding batches available.
Here is my code so far :

Sub UserForm_Initialize()
Dim ws_bl As Worksheet
Set ws_bl = Worksheets("Sheet1")

For Each cLoc In ws_bl.Range("filtered_products#")
 With Me.cboMaterial
 .AddItem cLoc.Value
  End With
Next cLoc
End Sub

It works fine so far, but I cannot repeat this process for CboBatch with the corresponding batches.

I tried inserting this formula

:="=FILTER(batch,(product=" & CboProduct.Value & ")*(balance<>0)"

in the Rowsource section of properties of CboBatch, but it returns an error (RowSource seems to only take ranges...)

Does anyone have an idea how to do it ?

Many thanks in advance for your help !


Akim

 

[Akuini]

@akim
Welcome to MrExcel.
This is an example of 3 dependent combobox in a userform:

3 combobox, dependent, userform, simple, dictionary, table - 2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

The code:

'====YOU MAY NEED TO ADJUST THE CODE IN THIS PART:====
'sheet's name where the list  is located.
Private Const sList As String = "Sheet1"
'Table name where the list  is located
Private Const tbl As String = "Table1"
Dim vList
Dim d As Object

Private Sub UserForm_Initialize()
    vList = Sheets(sList).ListObjects("Table1").DataBodyRange.Columns("A:C")
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare

End Sub

Private Sub ComboBox1_Change()
       ComboBox2.Value = ""
       ComboBox3.Value = ""
End Sub

Private Sub ComboBox2_Change()
       ComboBox3.Value = ""
End Sub

Private Sub ComboBox1_Enter()
Dim i As Long
   
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = Empty
    Next
       ComboBox1.List = d.keys
          d.RemoveAll

End Sub


Private Sub ComboBox2_Enter()
Dim i As Long
    
    For i = LBound(vList) To UBound(vList)
        If UCase(vList(i, 1)) = UCase(ComboBox1.Value) Then d(vList(i, 2)) = Empty
    Next
       ComboBox2.List = d.keys
          d.RemoveAll

End Sub

Private Sub ComboBox3_Enter()
Dim i As Long
    For i = LBound(vList) To UBound(vList)
    If UCase(vList(i, 1)) = UCase(ComboBox1.Value) And UCase(vList(i, 2)) = UCase(ComboBox2.Value) Then
        d(vList(i, 3)) = Empty
    End If
    Next
       ComboBox3.List = d.keys
          d.RemoveAll

End Sub

Note: all comboboxes Rowsource must be blank.

 

[akim]

Thank you very much Akuini for your input.

it seems to solve the first part of my post (i.e. the dependend dropdowns on the userform), however any idea how I can exclude from the dropdown menu the items that have 0 kg in stock?

as an analogy in the file that you shared : it would exclude from the dropdown lists all the reps that have 0 sales

For that I tried to activate a filter on the table upon initialization of the userform but the results are not reflected in the dropdowns :

Private Sub UserForm_Initialize()
With Sheets(sList).ListObjects("Table1").DataBodyRange.Columns("A:C")
.AutoFilter Field:=4, Criteria1:="0"
    vList = Sheets(sList).ListObjects("Table1").DataBodyRange.Columns("A:C")
    Set d = CreateObject("scripting.dictionary")
End With
    
    d.CompareMode = vbTextCompare
End Sub

Any workaround?

 

[Akuini]

Could you explain what you're trying to do step by step?
Do you filter data in the sheet then open the userform and have the filtered data populated in the combobox?
OR
Data in the sheet isn't filtered then you open the userform and then you filter data in the combobox?

And what kind of filter is it? Does it include "no match criteria" such as "<>" ?

 

[akim]

Hi Akuini,

I posted a few days ago a response, but it seems like my post did not go through.
I will use the example of the file you shared with me for more clarity

Step 1 : I open the excel file; the table is unfiltered
Step 2 : I open the userform
Step 3 : I click on the combobox1 and the list of "CITY" appears. I select a city
Step 4 : I click on combobox 2 and the list of REPS appears. This list must be filtered with criteria "Sales"<>0.
i.e. REPS who have sales of $0 do not appear on that list.

 

[Akuini]

This list must be filtered with criteria "Sales"<>0

Do you plan to use only this exact criteria, i.e. "Sales"<>0? or it could be different, such as "Sales">5 or "Sales"= 10.

 

[akim]

Do you plan to use only this exact criteria, i.e. "Sales"<>0? or it could be different, such as "Sales">5 or "Sales"= 10.

Yes I plan to use only the criteria "Sales <>0".

thanks