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]

Why not keep account combo as a single column & reference that along with the vendor combo, it keeps things a lot simpler.

 

[Kyletok]

Why not keep account combo as a single column & reference that along with the vendor combo, it keeps things a lot simpler.

Sorry I should have clarified. I didn't want to over complicate my explanation so I just used 2 columns to explain what I need, I now realize that actually just over complicated it.

what actually is happening is we are breaking up the accounts for every analyst in the team - so that the only accounts they have access to is the one assigned to them.

Since one account number can exist more than once across different vendors, the only way to identify the correct row is by using Vendor and Account at the same time. So:
Account: 001 and Vendor: 1 & Account: 001 and Vendor:2 can actually belong to different analysts.

Every analysts will have a copy of this excel sheet which is pulling (by data query) data from a masterfile we have on our sharedrive.

this is what I have right now:

Option Explicit
Dim DICT As Object

End Sub
Private Sub UserForm_Initialize()
   Dim Analyst As String
   Analyst = Sheets("Function_Reference").Range("b11") 
[COLOR=#008000]'Get Analyst Name, from a hidden reference sheet.[/COLOR]
   
   Dim KEY As Variant
   Set DICT = CreateObject("Scripting.dictionary")
   For Each KEY In [Accounts].Columns(4).Rows
[COLOR=#008000]'The analyst name is in column 4, so I adjusted the offset accordingly.[/COLOR]
      If Not DICT.Exists(KEY.Value) Then
         Set DICT(KEY.Value) = CreateObject("scripting.dictionary")
      End If
      DICT(KEY.Value)(KEY.Offset(, -3).Value) = ""
   Next KEY
   
   Me.ComboBox1.List = DICT(Analyst).Keys
[COLOR=#008000]'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.[/COLOR]
End Sub

So what's happening is, Combobox1 (or AccountBox in my form) is using 2 columns.
then when completing the userform is complete with a button press I just get the information with

[COLOR=#574123][Invoices].Rows(x).Columns(c) = Me.ComboBox1.value[/COLOR]
[COLOR=#574123][Invoices].Rows(x).Columns(c+1) = Me.ComboBox1.column(1) [/COLOR]

I do it like that in case an analyst has a duplicate account number but with two separate vendors.

I ofcourse have other input boxes and such in the form and therefore other macros, but non of those are relevant to this issue.

sorry for not clarifying, I was hoping I would be able to break down and learn whatever I got from here and figure it out myself.

the macro is almost perfect, I just need it to have the 2 columns and its just what I am looking for.

 

[Fluff]

So despite the title to the thread you are not actually using dependant combos, is that correct?

 

[Kyletok]

We might still want to use Analyst name as a ComboBox in case one of them is going to be off and we want them to work on another analyst's account. Honestly I am just being confusing, I apologize.

right now in the macro I am just pulling the analyst name from a reference sheet. But if you can help me figure out how I would go about having an analyst combobox filter through the account combobox (which is using two columns!)

You showed me how to get unique values from a column (thanks a bunch!) which I am using for the Analyst Combobox (Right now it just drops it to Sheets("Function_Reference").Range("b11") because I am planning on using it later)

But ultimately what I have been trying to achieve is to filter through [Accounts].Columns("A:B").Value, and only include the rows that match the value of column x (column 4 in my case) whether it will be selected through another combobox or pulled from the spreadsheet.

Preferably I would like the solution that would pull it from the analyst combobox.

Edit: I just want to say, thanks a lot for your help! I am learning a lot here and am already using these macro bits to get unqiue values and to filter through some listboxes that I have - that DON'T require 2 columns, and it works perfectly!

 

[Fluff]

Ok how about

Private Sub UserForm_Initialize()
   Dim Analyst As String
   Dim Cl As Range

   Analyst = Sheets("Function_Reference").Range("b11")
'Get Analyst Name, from a hidden reference sheet.
   
   Set Dict = CreateObject("Scripting.dictionary")
   For Each Cl In [Accounts].Columns(4).Rows
'The analyst name is in column 4, so I adjusted the offset accordingly.
      If Not Dict.Exists(Cl.Value) Then
         Set Dict(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dict(Cl.Value)(Cl.Offset(, -3).Value) = Cl.Offset(, -2).Value
   Next Cl
   Me.ComboBox1.List = Application.Transpose(Array(Dict(Analyst).Keys, Dict(Analyst).Items))
'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.
End Sub

I would also strongly recommend against using VBA keywords (such as Key) for names of variables, as it can cause problems.

 

[Kyletok]

Ok how about

Private Sub UserForm_Initialize()
   Dim Analyst As String
   Dim Cl As Range

   Analyst = Sheets("Function_Reference").Range("b11")
'Get Analyst Name, from a hidden reference sheet.
   
   Set Dict = CreateObject("Scripting.dictionary")
   For Each Cl In [Accounts].Columns(4).Rows
'The analyst name is in column 4, so I adjusted the offset accordingly.
      If Not Dict.Exists(Cl.Value) Then
         Set Dict(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dict(Cl.Value)(Cl.Offset(, -3).Value) = Cl.Offset(, -2).Value
   Next Cl
   Me.ComboBox1.List = Application.Transpose(Array(Dict(Analyst).Keys, Dict(Analyst).Items))
'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.
End Sub

I would also strongly recommend against using VBA keywords (such as Key) for names of variables, as it can cause problems.

This is almost perfect. This works perfectly for analysts that have more than one account. But When an analyst only has one account (rare case but still) it breaks Account and Vendor into two separate options. Uploaded an image:
https://imgur.com/qcXLLC6

And thank you for the tip, I'll avoid using "key".

Edit: actually found another issue.

I have an account that is the same account number but two different vendors for the same analyst.

it should appear as
Account - Vendor - Analyst
111111 -- Peanut -- John
111111 -- Banana -- John

But I noticed it only pulled the Banana account and not the Peanut account.

The account will appear twice as it should in Me.ComboBox1.List = [Accounts].Columns("A:B") since it didn't find unique account numbers it just straight up gave the list of accounts, which is what I need except filtered depending on Analyst.

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Dim Analyst As String
   Dim Cl As Range
   Dim Ary As Variant
   
   Analyst = Sheets("Function_Reference").Range("b11")
'Get Analyst Name, from a hidden reference sheet.
   
   Set Dict = CreateObject("Scripting.dictionary")
   For Each Cl In [Accounts].Columns(4).Rows
'The analyst name is in column 4, so I adjusted the offset accordingly.
      If Not Dict.Exists(Cl.Value) Then
         Set Dict(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dict(Cl.Value)(Cl.Offset(, -3).Value) = Cl.Offset(, -2).Value
   Next Cl
   If Dict(Analyst).Count = 1 Then
      ReDim Ary(1 To 1, 1 To 2)
      Ary(1, 1) = Dict(Analyst).Keys()(0): Ary(1, 2) = Dict(Analyst).Items()(0)
   Else
      Ary = Application.Transpose(Array(Dict(Analyst).Keys, Dict(Analyst).Items))
   End If
   Me.ComboBox1.List = Ary
'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.
End Sub

 

[Kyletok]

How about

Private Sub UserForm_Initialize()
   Dim Analyst As String
   Dim Cl As Range
   Dim Ary As Variant
   
   Analyst = Sheets("Function_Reference").Range("b11")
'Get Analyst Name, from a hidden reference sheet.
   
   Set Dict = CreateObject("Scripting.dictionary")
   For Each Cl In [Accounts].Columns(4).Rows
'The analyst name is in column 4, so I adjusted the offset accordingly.
      If Not Dict.Exists(Cl.Value) Then
         Set Dict(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Dict(Cl.Value)(Cl.Offset(, -3).Value) = Cl.Offset(, -2).Value
   Next Cl
   If Dict(Analyst).Count = 1 Then
      ReDim Ary(1 To 1, 1 To 2)
      Ary(1, 1) = Dict(Analyst).Keys()(0): Ary(1, 2) = Dict(Analyst).Items()(0)
   Else
      Ary = Application.Transpose(Array(Dict(Analyst).Keys, Dict(Analyst).Items))
   End If
   Me.ComboBox1.List = Ary
'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.
End Sub

I still have this issue:

I have an account that is the same account number but two different vendors for the same analyst.

it should appear as
Account - Vendor - Analyst
111111 -- Peanut -- John
111111 -- Banana -- John

But I noticed it only pulled the Banana account and not the Peanut account.

The account will appear twice as it should in Me.ComboBox1.List = [Accounts].Columns("A:B") since it didn't find unique account numbers it just straight up gave the list of accounts, which is what I need except filtered depending on Analyst.

 

[Fluff]

You're quite right, that comes of bodging code, rather than thinking about things properly.
Try

Private Sub UserForm_Initialize()
   Dim Analyst As String
   Dim Cl As Range
   Dim Ary As Variant
   Dim Valu As String
   
   Analyst = Sheets("Function_Reference").Range("b11")
'Get Analyst Name, from a hidden reference sheet.
   
   Set Dict = CreateObject("Scripting.dictionary")
   For Each Cl In [Accounts].Columns(4).Rows
'The analyst name is in column 4, so I adjusted the offset accordingly.
      If Not Dict.Exists(Cl.Value) Then
         Set Dict(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      Valu = join(Application.Index(Cl.Offset(, -3).Resize(, 2).Value, 1, 0), " ")
      Dict(Cl.Value)(Valu) = Array(Cl.Offset(, -3).Resize(, 2).Value)
   Next Cl
   If Dict(Analyst).Count = 1 Then
      Ary = Dict(Analyst).Items()(0)(0)
   Else
      Ary = Application.Index(Dict(Analyst).Items, 0, 0)
   End If
   Me.ComboBox1.List = Ary
'Load the userform with the correct list immedietly. There's no actual need to modify it while using the list since the analyst name is already defined.
End Sub

 

[Kyletok]

unfortunately the approach doesn't seem to work, the combobox loads blank.

I have made a new post because the answer for the original question I asked on here is not what I am looking for anymore, I know how to have a combobox depend on another combobox, and your first answer works perfectly for one of my forms which does need to pull unique values and depend on another combobox. I'm sorry for breaking the forum rules I was just trying to find a solution by being more clear this time.

ultimately what I need is a 2 column combobox to just depend on a value on column 4.
(only pull rows where column 4 = "Name")