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

 

[My Aswer Is This]

So in Vender Combobox you only want to see value found in Column (1)

And are you saying it may find more then one ???

So when user selects George in AccountBox

Script looks down Table Column(2) for George and enters value in VenderBox That was found adjacent column(1)

Is that what you want.

 

[My Aswer Is This]

If that is what you want try this:
I'm assuming it will only find the search value once.

Private Sub AccountBox_Click()
'Modified  1/29/2019  5:55:16 PM  EST
Set SearchRange = [Accounts].Columns(2).Find(AccountBox.Value)
If SearchRange Is Nothing Then MsgBox AccountBox.Value & "  Not Found": Exit Sub
VendorBox.Clear
VendorBox.AddItem SearchRange.Offset(, -1).Value
End Sub
Private Sub UserForm_Initialize()
AccountBox.List = [Accounts].Columns(2).Value
End Sub

 

[My Aswer Is This]

If you wanting to search column(2) of the table
And if value is found put value from adjacent column(1) into Combobox VendorBox

And there may be more then one. Try using these two scripts.

Private Sub AccountBox_Click()
'Modified  1/29/2019  10:13:14 PM  EST
Dim ans As String
Dim myTable As ListObject
Dim myArray As Variant
ans = AccountBox.Value
Set myTable = ActiveSheet.ListObjects("Accounts")
Set myArray = myTable.ListColumns(2).Range
VendorBox.Clear
For Each cell In myArray
    If cell.Value = ans Then VendorBox.AddItem cell.Offset(, -1).Value
Next cell
    
End Sub

 

[Fluff]

How about

[COLOR=#0000ff]Option Explicit
Dim Dic As Object[/COLOR]

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
End Sub
Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("[COLOR=#ff0000]List[/COLOR]")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.ListObjects("Accounts").ListColumns(1).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
End Sub

The two lines in blue must go at the very top of the module, before any code.
Change sheet name & Combo names to suit

 

[My Aswer Is This]

And what was there about my script which you thought was not a good way to do this.

How about

[COLOR=#0000ff]Option Explicit
Dim Dic As Object[/COLOR]

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
End Sub
Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("[COLOR=#ff0000]List[/COLOR]")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.ListObjects("Accounts").ListColumns(1).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
End Sub

The two lines in blue must go at the very top of the module, before any code.
Change sheet name & Combo names to suit

 

[Fluff]

What makes you think that I felt there was a problem with your solution?

 

[Kyletok]

sorry guys that's actually not what I am looking for.

lets say I have a table like this:
[TABLE="width: 179"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD]Account1[/TD]
[TD]Vendor1[/TD]
[/TR]
[TR]
[TD]Account2[/TD]
[TD]Vendor2[/TD]
[/TR]
[TR]
[TD]Account3[/TD]
[TD]Vendor2[/TD]
[/TR]
[TR]
[TD]Account4[/TD]
[TD]Vendor2[/TD]
[/TR]
[TR]
[TD]Account5[/TD]
[TD]Vendor3[/TD]
[/TR]
[TR]
[TD]Account6[/TD]
[TD]Vendor3[/TD]
[/TR]
[TR]
[TD]Account7[/TD]
[TD]Vendor3[/TD]
[/TR]
[TR]
[TD]Account8[/TD]
[TD]Vendor3[/TD]
[/TR]
[TR]
[TD]Account9[/TD]
[TD]Vendor4[/TD]
[/TR]
[TR]
[TD]Account10[/TD]
[TD]Vendor4[/TD]
[/TR]
</tbody>[/TABLE]

Currently the combobox is loaded with:
Private Sub UserForm_Initialize()
AccountBox.List = [Accounts].Columns(1).Value
End SubWhich takes all the account rows from my table named "Accounts"
So the option in the account combobox are:
[TABLE="width: 99"]
<tbody>[TR]
[TD]AccountBox[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 99"]
<tbody>[TR]
[TD]Account1[/TD]
[/TR]
[TR]
[TD]Account2[/TD]
[/TR]
[TR]
[TD]Account3[/TD]
[/TR]
[TR]
[TD]Account4[/TD]
[/TR]
[TR]
[TD]Account5[/TD]
[/TR]
[TR]
[TD]Account6[/TD]
[/TR]
[TR]
[TD]Account7[/TD]
[/TR]
[TR]
[TD]Account8[/TD]
[/TR]
[TR]
[TD]Account9[/TD]
[/TR]
[TR]
[TD]Account10[/TD]
[/TR]
</tbody>[/TABLE]

I want to make a second combobox that will filter through the option in AccountBox combo box.
So if I selected Vendor2 in VendorBox combobox, AccountBox will be loaded with:
[TABLE="width: 99"]
<tbody>[TR]
[TD]AccountBox[/TD]
[/TR]
[TR]
[TD]Account2[/TD]
[/TR]
[TR]
[TD]Account3[/TD]
[/TR]
[TR]
[TD]Account4[/TD]
[/TR]
</tbody>[/TABLE]

if I picked Vendor3 in VendorBox, AccountBox will be loaded with:
AccountBox[TABLE="width: 99"]
<tbody>[TR]
[TD]Account5[/TD]
[/TR]
[TR]
[TD]Account6[/TD]
[/TR]
[TR]
[TD]Account7[/TD]
[/TR]
[TR]
[TD]Account8[/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes sense. I want to be able to filter through the options.
what bit of code can I add to AccountBox.List = [Accounts].Columns(1).Value so that it only shows the options where column(2) = "Vendor1" or whatever else?

Hope this made sense, Thanks again in advance!

 

[Fluff]

Change initialize event to

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([COLOR=#ff0000]2[/COLOR]).DataBodyRange
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dic(Cl.Value)(Cl.Offset(,[COLOR=#ff0000] -1[/COLOR]).Value) = Empty
   Next Cl
   Me.ComboBox1.List = Dic.Keys
End Sub

 

[Kyletok]

I'm actually hoping for it to change whenever VendorBox changes, so I believe it should be in AfterUpdate event, and initalize will just remain AccountBox.List = [Accounts].Columns(1).Value to pull all the accounts if VendorBox is empty.


Set ws = Sheets("List")
do I set was as [Accounts].Columns(A:B).Value? (I would like to keep it so it refers to the table directly)