When any cell is selected in column A, the hidden listbox appears.The data received from other sheet (List sheet) is sorted on the listbox.
The data selected from this listbox is easily entered into the active cell.
The used codes :
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
If Not Intersect(Range("A:A"), Target) Is Nothing And Target.Count = 1 And Target.Address(False, False) <> "A1" Then
If ActiveCell.Row >= 9 Then
ActiveWindow.ScrollRow = ActiveCell.Row - 8
End If
Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox1.List = Sheets("List").Range("A2:A" & Sheets("List").Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = 0 To Me.ListBox1.ListCount - 1
If Target.Value <> Empty And Me.ListBox1.List(i, 0) = Target.Value Then
Me.ListBox1.Selected(i) = True
End If
Next i
Me.ListBox1.Top = Target.Top
Me.ListBox1.Left = Target.Left + Target.Width
Me.ListBox1.Visible = True
Else
Me.ListBox1.Visible = False
End If
i = Empty
End Sub
Private Sub ListBox1_Change()
Dim yaz As String
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
yaz = yaz & Me.ListBox1.List(i) & " "
End If
Next i
ActiveCell.Value = Trim(yaz)
End Sub "
