Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([g9:G100], Target) Is Nothing And Target.Count = 1 Then
Set f = Sheets(1)
Set d = CreateObject("scripting.dictionary")
Set Rng = f.Range("B6:B" & f.[B65000].End(xlUp).Row)
For Each c In Rng
If c = Target.Offset(, -1) Then tmp = c.Offset(, 1).Value: d(tmp) = ""
Next c
Me.ComboBox1.List = d.keys
Me.ComboBox1.Height = Target.Height + 3
Me.ComboBox1.Width = Target.Width
Me.ComboBox1.Top = Target.Top
Me.ComboBox1.Left = Target.Left
Me.ComboBox1 = Target
Me.ComboBox1.Visible = True
Me.ComboBox1.Activate
Else
Me.ComboBox1.Visible = False
End If
End Sub
Private Sub ComboBox1_click()
ActiveCell = Me.ComboBox1
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(1).Select
End Sub