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
We created a separate template based on the listbox single select feature :