VBA combobox modification with if statement

[Pojzon]

Hey there,
I've got something like this:

I need modification to this code:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Activate()    
Dim a, e
    a = Sheets("Sheet1").Cells(1).CurrentRegion.Columns(1).Offset(1).Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each e In a
            If e <> "" Then .Item(e) = Empty
        Next
        Me.ComboBox1.List = .keys
    End With 
End Sub
</code>

It fills my combobox1 with unique employee names, but I would like it to also take into account company name definied by combobox2 and linked into F2 cell. So something like if F2 = "XYZ" then fill combobox1 with unique employee names in that company else fill names with "ZYX" company.
Any help is greatly appreciated. Thank you!

Crossposted at: https://www.excelforum.com/excel-programming-vba-macros/1249395-if-statement-in-combobox.html

 

[steve the fish]

Its impossible to answer your question with what you have given. There are employee names in column A and a company name in F2. How are the employees matched to the company?

 

[Pojzon]

They are matched in sheet1 (screen) employees are in column A and company in column B. One employee is always in only one company.

 

[steve the fish]

Heres how you could get the cell values into the dictionary if they pass the test:

Dim lr As Long, d As Object, c As Range, k, s As String

lr = Range("A" & Rows.Count).End(xlUp).Row
Set d = CreateObject("scripting.dictionary")

For Each c In Range("A2:A" & lr)
    If c.Offset(0, 1).Value = Range("F2").Value Then
        s = Trim(c.Value)
        If Len(s) > 0 Then d(s) = d(s) + 1
    End If
Next

For Each k In d.keys
    Debug.Print k
Next

 

[Pojzon]

Works great, thank you very much for help!