Kapat

Show Listbox When Cell Is Selected

vba select listbox enter to cell

vba display listbox if 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.

excel show listbox when cell selected

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. excel vba listbox multiselect Me.ListBox1.MultiSelect = fmMultiSelectMulti

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

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.

vba display listbox cell select

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 : excel fill listbox 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 : excel listbox single selectexcel listbox single select

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