Private Sub UserForm_Initialize()
Set f = Sheets("Listing")
Set MonDico = CreateObject("Scripting.Dictionary")
For Each c In Range(f.[A2], f.[A65000].End(xlUp))
If c.Value <> "" Then MonDico.Item(c.Value) = c.Value
Next c
Me.ComboBox1.List = MonDico.items
SendKeys "{F4}"
End Sub
Private Sub ComboBox1_Change()
Set f = Sheets("Listing")
i = 0
Me.ComboBox2.Clear
For Each c In Range(f.[A2], f.[A65000].End(xlUp))
If c = Me.ComboBox1 Then
Me.ComboBox2.AddItem
Me.ComboBox2.List(i, 0) = c.Offset(, 1).Value
Me.ComboBox2.List(i, 1) = c.Offset(0, 2).Value
Me.ComboBox2.List(i, 2) = c.Offset(0, 3).Value
Me.ComboBox2.List(i, 3) = c.Offset(0, 4).Value
Me.ComboBox2.List(i, 4) = c.Offset(0, 5).Value
Me.ComboBox2.List(i, 5) = c.Offset(0, 6).Value
Me.ComboBox2.List(i, 6) = c.Offset(0, 7).Value
Me.ComboBox2.List(i, 7) = c.Offset(0, 8).Value
i = i + 1
End If
Next c
Me.ComboBox2.SetFocus
SendKeys "{F4}"
End Sub
Private Sub ComboBox2_Change()
If Me.ComboBox2.ListIndex > -1 Then
ActiveCell = Me.ComboBox1
ActiveCell.Offset(2) = Me.ComboBox2
ActiveCell.Offset(3) = Me.ComboBox2.Column(1)
ActiveCell.Offset(4) = Me.ComboBox2.Column(2)
ActiveCell.Offset(5) = Me.ComboBox2.Column(3)
ActiveCell.Offset(6) = Me.ComboBox2.Column(4)
ActiveCell.Offset(7) = Me.ComboBox2.Column(5)
ActiveCell.Offset(8) = Me.ComboBox2.Column(6)
End If
Unload Me
End Sub