VBA QUESTION, dependant combobox on another combobox in Userform

[Kyletok]

I tried to google this, but didn't know how to word it properly so couldn't find a proper non complicated solution.

I have a table called 'Accounts' with all the account information.
Right now when I open the userform I have:

Private Sub UserForm_Initialize()
AccountBox.List = [Accounts].Columns(2).Value
End Sub

Which loads it with all the account numbers.

What if I had a combobox above it for Vendor?

VendorBox.List = [Accounts].Columns(1).Value

Can I somehow only show the accounts for the chosen vendor in AccountBox?
(I feel I am wording this wrong, can I filter it so it only shows the rows with the vendor?)

 

[Fluff]

I'm actually hoping for it to change whenever VendorBox changes

That is exactly what my code does.
However if you want the account Combo to have the full list if the vendor is empty try

Option Explicit
Dim Dic As Object

Private Sub ComboBox1_AfterUpdate()
   Me.ComboBox2.Clear
   If Me.ComboBox1.Value <> "" Then
      Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
   Else
      Me.ComboBox2.List = Sheets("List").ListObjects("Accounts").ListColumns(1).DataBodyRange.Value
   End If

End Sub
Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("List")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.ListObjects("Accounts").ListColumns(2).DataBodyRange
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dic(Cl.Value)(Cl.Offset(, -1).Value) = Empty
   Next Cl
   Me.ComboBox1.List = Dic.Keys
   Me.ComboBox2.List = Sheets("List").ListObjects("Accounts").ListColumns(1).DataBodyRange.Value
End Sub

 

[Kyletok]

it works, but kind of hard to integrate into a userform that already has a bunch of macros.

can you explain the importance of Option Explicit? I'm trying to learn what you did in that code and mostly am having a hard time understanding what is happening with the Dic variable.

I appreciate you writing it for me by the way! I'm definitely going to use it, I just want to understand it too

 

[Kyletok]

also I would actually like to show the second column when looking in the list,
Me.ComboBox2.List = Sheets("List").ListObjects("Accounts").ListColumns(1).DataBodyRange.Value
how do I make this row show 2 columns?
earlier I had
ComboBox2.List = [Accounts].Columns("A:B").Value
which worked but "A:B" doesn't work with ListColumns

edit:
here too
for Each Cl In [Accounts].ListObject.ListColumns(2).DataBodyRange

Sorry I should have pointed out that my Account Combobox has 2 columns in it, so it shows which vendor is for each account.

 

[Fluff]

Option Explicit simply makes you declare all your variables, which is good practise as it will also help to spot any typos.

If this

ComboBox2.List = [Accounts].Columns("A:B").Value

works for you, then go with it.

Regarding the Dictionary have a look here https://excelmacromastery.com/vba-dictionary/

 

[Kyletok]

Option Explicit simply makes you declare all your variables, which is good practise as it will also help to spot any typos.

If this

ComboBox2.List = [Accounts].Columns("A:B").Value

works for you, then go with it.

Regarding the Dictionary have a look here https://excelmacromastery.com/vba-dictionary/

it works yeah but now the ComboBox1_AfterUpdate event will update combobox2 without the second column, how do I fix that?

I basically have it like that when selecting an account:

https://imgur.com/Jt6HZUj

 

[Fluff]

You can try this, whilst it will give both values it will only be 1 column

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet
   Dim Valu As String

   Set ws = Sheets("List")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.ListObjects("Accounts").ListColumns(2).DataBodyRange
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Valu = join(Application.Index(Cl.Offset(, -1).Resize(, 2).Value, 1, 0), " ")
      Dic(Cl.Value)(Valu) = Empty
   Next Cl
   Me.ComboBox1.List = Dic.Keys
   Me.ComboBox2.List = [Accounts].Columns("A:B").Value
End Sub

 

[Kyletok]

Okay thank you! this works and I'm learning and tweaking it as I need it.

Could you just explain what is happening on this line?
Dic(Cl.Value)(Valu) = Empty
or even as you had it earlier
Dic(Cl.Value)(Cl.Offset(, -1).Value) = Empty

This is the line I can't really find an explanation online for, if you don't mind explaining it

 

[Fluff]

This line

Set Dic(Cl.Value) = CreateObject("scripting.dictionary")

will set the item as a new dictionary.
and then this line

Dic(Cl.Value)(Cl.Offset(, -1).Value) = Empty

will add a key to that new dictionary (which is the col A value) and leaves the item "empty"
HTH

 

[Kyletok]

Okay makes sense,

I got one more question:
Me.ComboBox2.List = DIC(Me.ComboBox1.Value).Keys

is there a way to instead depend on what's in combobox1, it will depend on lets say: Sheets("sheet1").Range("A1") ?
I tried making a variable but not sure what to dim it as

what I tried is
Dim A1 as String (If I dim it as anything else I get an error s: )
A1 = Sheets("sheet1").Range("A1")

and then I tried
Me.ComboBox2.List = DIC(A1).Keys

which didn't work.

EDIT: nvm I see what was wrong.

 

[Kyletok]

You can try this, whilst it will give both values it will only be 1 column

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet
   Dim Valu As String

   Set ws = Sheets("List")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.ListObjects("Accounts").ListColumns(2).DataBodyRange
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Valu = join(Application.Index(Cl.Offset(, -1).Resize(, 2).Value, 1, 0), " ")
      Dic(Cl.Value)(Valu) = Empty
   Next Cl
   Me.ComboBox1.List = Dic.Keys
   Me.ComboBox2.List = [Accounts].Columns("A:B").Value
End Sub

This adds the second column to the actual value, But I just want it to show the second column in the drop down, but once selected it's only the account number.

It's the way it was before the macros you wrote for me, had it as
Me.ComboBox2.List = [Accounts].Columns("A:B").Value
and in the columncount property I had 2 and in columnbound property I have 1.
this is important because the same account number can exist across different vendors,
I have it pull column 2 with
[Invoices].Rows(x).Columns(c) = Me.ComboBox2.value
[Invoices].Rows(x).Columns(c+1) = Me.ComboBox2.column(1)
which works with pulling both account number and vendor from a single combobox into different cells in my Invoices Table.

the
join(Application.Index(Cl.Offset(, -1).Resize(, 2).Value, 1, 0), " ")
just joins them into the same value, and therefore I can't export them to individual cells.

so is there a way to do the same filtering and keep the combobox with 2 columns and only 1 bound?