How to populate a listbox?

[usr7501]

Hello,

Is it possible to populate a listbox? If so how is it done in Excel?

 

[Chief Ace]

Go to Data Tab
Click Data Validation
Click Data Validation (again)
Under Settings Tab
Click drop arrow under "Allow"
Select "List"
Under the source click the blue square on the right
then highlight all the list.
click OK.

See sample.

Hopefully this is the one you are looking for.

 

[NARAYANK991]

Hi ,

See the attached file for two methods ; there are 3 macros named :

ThisWorkbook.ClearListboxes

ThisWorkbook.PopulateListruta2

ThisWorkbook.PopulateListruta3

Narayan

 

[Hui]

If your list of Fruit can have duplicates, you may want to consider using this approach which ensures that each fruit is only listed once

Sub PopulateListruta4()
    
    ' Create dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim cell As Range, sItem As String
    ' Go through each item in range
    For Each cell In [Fruits]
        sItem = Trim(cell.Value)
        ' check if item already exists in dictionary
        If dict.Exists(sItem) = False Then
            ' If doesn't exist then add to dictionary and combobox
            dict.Add sItem, 1
            Sheet1.Shapes("Listruta 4").ControlFormat.AddItem sItem
        End If
    Next

    ' Clean up dictionary as we no longer need it
    Set dict = Nothing

End Sub

or see attached file: