New to Excel VBA

[BryanPayne]

Hello everyone!

I have been asked to take a stab at creating a small GUI to look up data vs. Filtering data in excel. The reason I am doing it this way is because I will be summing totals as I click on certain people.

I created a drop-down box (cboSupervisor).
Once I click on a name in the drop-down box, i am needing it to populate the List box (lbManager) with all of the managers that fall under that specific manager.

The supervisor data resides in column E2:E2000. The Managers are listed in column F2:F2000.

I have the Supervisors populating the drop-down list on userform_initialize. The Supervisors name is duplicated so I am removing the duplicates. I havent been able to alphabetize the names either.

ex.

Private Sub UserForm_Initialize()

With Sheets("HoursDetailDateRangeExport").Range("e2:e2000")
v = .Value
End With

With CreateObject("scripting.dictionary")
.comparemode = 1

For Each e In v
If Not .exists(e) Then .Add e, Nothing
Next

If .Count Then Me.cboSupervisor.List = Application.Transpose(.keys)
End With

End Sub


Hopefully someone can get me going in the right direction.

Thanks in advance!

 

[Fluff]

As far as the sort goes, have a look here http://www.xl-central.com/sort-a-dictionary-by-key.html

How do you know which managers report to the names in the combobox

 

[BryanPayne]

Fluff,

The Supervisors, Managers and Employees are all listed in the spreadsheet by column.

Column B
Employee Last Name

Column C
Employee First Name

Column D
Employee Middle Initial

Column E
Employee Supervisor

Column F
Employee Manager


I have loaded the ComboBox when the UserForm Intitalizes. When the UserForm opens, I select the Supervisor from the dropdown. Once the Supervisor is selected, I am wanting to populate the ListBox with all of the Managers associated with the Supervisor.

This is where I am stuck.

 

[Fluff]

How about

Option Explicit
Private Dic As Object

Private Sub ComboBox1_Change()
   ListBox1.list = Application.Transpose(Dic(ComboBox1.Value).keys)
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range

   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("Sheet3")
      For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, CreateObject("scripting.dictionary")
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, 1).Value) Then
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         End If
      Next Cl
   End With
   ComboBox1.list = Application.Transpose(Dic.keys)
End Sub