Excel Automatically Displaying Listbox When Cell Is Selected

Excel VBA : Display Listbox While Cell Is Selected


          Easy way to enter data into cell with Listbox !
When any cell is selected in column A of Data sheet, the hidden listbox becomes visible.The received data from column A of List sheet  is sorted on the listbox.The selected item or items from this listbox are easily entered into the active cell.


Our VBA codes for to list  unique values on listbox  :
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

The number 1 in VBA codes above ( Cells(Rows.Count, 1) ,Sheets("List").Cells(x, 1) etc. ) refers to the first column - column A.

For sorting listbox items ascending order (from A to Z) :
With ListBox1
For i = 0 To .ListCount - 2
   For j = i + 1 To .ListCount - 1
     If .List(i) > .List(j) Then
     Temp = .List(j)
      .List(j) = .List(i)
      .List(i) = Temp
     End If
   Next j
Next i
End With

We set MultiSelect property for listbox control : 
Me.ListBox1.MultiSelect = fmMultiSelectMulti

We have added all the codes to the Worksheet_SelectionChange method of the Data sheet.




No comments:

Post a Comment