Show Listbox When Cell Is Selected

In order to easily enter data in the sheet, we added a listbox to the worksheet without using the userform and hid this listbox.

The listbox is filled with items on the other sheet (List sheet).
When any cell is selected in column A of main sheet (Data sheet), the listbox is displayed and the selected item or items from the listbox is entered into the cell. We have set the listbox multiselect property so that multiple listbox items can be selected and added to worksheet. Me.ListBox1.MultiSelect = fmMultiSelectMulti

To enter data into cell according to listbox item selection, we added the following codes to the ListBox Change procedure :

Private Sub ListBox1_Change()
Dim gir As String
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
            gir = gir & Me.ListBox1.List(i) & " "
        End If
    Next i
    ActiveCell.Value = Trim(gir)
End Sub

To select a cell in a column other than column A will hide the listbox again.

The user adds the values that he wants to list in the listbox to the List page, and the values on the List sheet are listed as sorted unique in the listbox.VBA codes to fill listbox with sorted unique values :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, x, j As Long, Temp As Variant
...               
Me.ListBox1.Clear
'Unique Records
For x = 2 To Sheets("List").Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Sheets("List").Range("A2:A" & x), Sheets("List").Cells(x, 1)) = 1 Then
ListBox1.AddItem Sheets("List").Cells(x, 1).Value
End If
Next

With ListBox1
    For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If UCase(.List(i)) > UCase(.List(j)) Then
                    Temp = .List(j)
                    .List(j) = .List(i)
                    .List(i) = Temp
             End If
     Next j
        Next i
    End With
...
End Sub

📥 Download sample Excel workbook that contains listbox control

We created a separate template based on the listbox single select feature :

📥 Download template that it is created with based on single selection listbox