VBA QUESTION, dependant combobox on another combobox in Userform

Kyletok

New Member
Joined
Sep 16, 2017
Messages
47
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:

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
If that is what you want try this:
I'm assuming it will only find the search value once.

Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
How about
Code:
[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
 
Upvote 0
And what was there about my script which you thought was not a good way to do this.
How about
Code:
[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
 
Upvote 0
What makes you think that I felt there was a problem with your solution?
 
Upvote 0
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!
 
Last edited:
Upvote 0
Change initialize event to
Code:
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top