Excel VBA - Creating a ListBox

Question

How do you create (not modify/populate) a ListBox in Excel VBA? I need to generate various ListBoxes for each cell in a column, doing it without VBA would be impractical. I'm looking for something like:

Dim lb as ListBox
' Populate ListBox Here '

worksheet.Cells(1,1) = lb ' Apply ListBox to cell '

I've googled this thoroughly (maybe my terminology is wrong) but I cant find a single reference to CREATING a listbox, and applying it to various cells.

Thanks!

EDIT: It should be noted that Dim lb As MSForms.ListBox throws the error

"User-defined type not defined"

. Maybe this is how to create ListBoxes... but I need to include this library????? I don't know how to do that though...

Each cell in a column should have a listbox : What data do you want to feed into these each listbox?And here is how to create a listbox — bonCodigo
Can you use Data Validation instead? That typically works for collecting choices on a by-row basis. — Doug Glancy
If you actually looked through the Google search your recommended, you'd see there is no explanation on how to create a ListBox purely with VBA. I must create the ListBoxes purely with VBA. I'm generating a Spreadsheet automatically, the end user should not have to manually insert ListBoxes. — user985779

GeorgeVremescu GeorgeVremescu · Accepted Answer · 2013-01-23T14:48:24

If you know how to create the listbox from Excel, not using VBA, just record one macro with you doing it and then check the resulted code.

LE: This is what i could record. The source values were on the same sheet as the destination list, but i think you can take over from here and expand as needed.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=$A$1:$A$6"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Excel VBA - Creating a ListBox

1 Answers