How to Populate a Combobox

Question

I'm quite new to VBA and I've been struggling with populating a combobox. I'm trying to fill a combobox with the contents of the first column in a spreadsheet so I can delete the associated row of data based on the combobox selection.

I've looked through several questions both here and elsewhere when making this question, but I haven't found anything that worked.

• populate combobox in VBA with array elements
• How do I populate a combo box from a column in my excel spread sheet?
• http://www.techrepublic.com/blog/microsoft-office/populate-a-userform-combo-box-in-excel-with-a-dynamic-list/

Below is the code I've tried. I'm somewhat lost as I've been trying to cobble together the different answers from other questions in order to get this to work, but to no avail. I expect the combobox to populate with the values from column 1, but it remains blank.

Attempt #1 This involved creating a dynamic range:

=OFFSET(PC_DataSheet!$A$2,0,0, COUNTA(PC_DataSheet!$A$1:$A$65536)-1,1)

Private Sub UserForm1_Initialize()

    Dim rngPCNumber As Range
    Dim ws As Worksheet

    Set ws = Worksheets("Sheet1")

    For Each rngPCNumber In ws.Range("PCNumber")
        Me.PC_ListComboBox.AddItem rngPCNumber.Value
    Next rngPCNumber

End Sub

Attempt #2

Private Sub UserForm1_Initialize()

    Dim arr() As Variant

    arr = Worksheets("Sheet1").Range("C2:" & lrow).Value
    PC_ListComboBox.List = arr

End Sub

Attempt #3

Private Sub UserForm1_Initialize()

    Dim vArr As Variant
    Dim i As Integer

    vArr = Sheet1.Range("A:1").Value

    With PC_ListComboBox.Clear
         For i = LBound(vArr) To UBound(vArr)
            .AddItem vArr(i)
         Next i
    End With

End Sub

Any help on this would be really appreciated!

---

EDIT: I've tried inserting the code suggested by Gary's Student into my UserForm_Initialize() Sub, but when I try to open the userform I get the following error message:

Run-time error '9': Subscript out of range

When I click debug, it highlights this code:

'Opens PC UserForm when pressed.
Private Sub AddPCButton_Click()

    UserForm.Show 'This line is the line highlighted by the debugger.

End Sub

I have no idea what's causing this...when I use the suggested code, I get an error message, but when I remove the code the userform functions flawlessly. Here's Private Sub UserForm_Initialize() with and without the suggested code.

'Clears and Initializes the form when first loaded.
Private Sub UserForm_Initialize()

    'Empties combo boxes.
    PC_OSTypeComboBox = ""
    PC_HDTypeComboBox = ""

    'Populates combo boxes.
    With PC_OSTypeComboBox
        .Clear
        .AddItem "Windows 8"
        .AddItem "Windows 7"
        .AddItem "Windows Vista"
        .AddItem "Windows XP"
        .AddItem "Windows 2000"
        .AddItem "Windows 98"
        .AddItem "Windows NT"
        .AddItem "Windows 95"
    End With
    With PC_HDTypeComboBox
        .Clear
        .AddItem "SATA"
        .AddItem "IDE"
        .AddItem "SCSI"
    End With

End Sub

This is including the suggested code:

'Clears and Initializes the form when first loaded.
Private Sub UserForm_Initialize()

    Dim N As Long, i As Long
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    With PC_NumberComboBox
        .Clear
        For i = 1 To N
            .AddItem Sheets("Sheet1").Cells(i, 1).Value
        Next i
    End With

    'Empties combo boxes.
    PC_OSTypeComboBox = ""
    PC_HDTypeComboBox = ""

    'Populates combo boxes.
    With PC_OSTypeComboBox
        .Clear
        .AddItem "Windows 8"
        .AddItem "Windows 7"
        .AddItem "Windows Vista"
        .AddItem "Windows XP"
        .AddItem "Windows 2000"
        .AddItem "Windows 98"
        .AddItem "Windows NT"
        .AddItem "Windows 95"
    End With
    With PC_HDTypeComboBox
        .Clear
        .AddItem "SATA"
        .AddItem "IDE"
        .AddItem "SCSI"
    End With

End Sub

Solution 1

Here is a super simple example of creating and filling a Forms style combo-box:

Sub FormsStyleComboBox()
    ActiveSheet.DropDowns.Add(411, 14.25, 124.5, 188.25).Select
    N = Cells(Rows.Count, "A").End(xlUp).Row
    strng = Range("A1:A" & N).Address
    Selection.ListFillRange = strng
End Sub

For example:

EDIT#1

I created a UserForm called Demo containing a combo-box called MyBox

In a Standard Module I put:

Sub DisplayIt()
    Demo.Show
End Sub

and in the UserForm code area:

Private Sub UserForm_Initialize()
    Dim N As Long, i As Long
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    With MyBox
        .Clear
        For i = 1 To N
            .AddItem Sheets("Sheet1").Cells(i, 1).Value
        Next i
    End With
End Sub

Running DisplayIt() produces:

This is based on this tutorial

Solution 2

Private Sub UserForm_Initialize()

    Dim CS As Integer
    Dim CR As Integer
    Dim RF As Integer
    Dim PW As Integer
    Dim CD As Integer

        CS = ActiveWorkbook.Sheets("LISTS").Columns(2).End(xlDown).Row      
        CR = ActiveWorkbook.Sheets("LISTS").Columns(3).End(xlDown).Row      
        RF = ActiveWorkbook.Sheets("LISTS").Columns(4).End(xlDown).Row      
        PW = ActiveWorkbook.Sheets("LISTS").Columns(5).End(xlDown).Row      
        CD = ActiveWorkbook.Sheets("LISTS").Columns(6).End(xlDown).Row      

    With CB_CS
        .Clear
        For i = 2 To CS + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 2).Value
        Next i
    End With

    With CB_CR
        .Clear
        For i = 2 To CR + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 3).Value
        Next i
    End With

    With CB_RF
        .Clear
        For i = 2 To RF + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 4).Value
        Next i
    End With

    With CB_PW
        .Clear
        For i = 2 To PW + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 5).Value
        Next i
    End With

    With CB_CD
        .Clear
        For i = 2 To CD + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 6).Value
        Next i
    End With
End Sub

The code above is located in my UserForm Code (Right-Click on UserForm, then click 'view code')

I created a Worksheet call LISTS. Each column on that sheet is for a different combo-box. Once I filled it out and got the code working I hid the LISTS worksheet.

Each ComboBox I named CB_XX so note those names in the code

I start the code by defining the length of the lists (note this fails if you only have one item in the list but if you only have one item don't use a combo box)

Once I get the lengths I add the correct columns to the correct comboboxes. Note the +1 in each for/next loop. That is to add a blank at the end of each list to allow the user to empty the selection. Remove the +1 if you don't want that blank. I start at i = 2 to not show the header row on my LISTS sheet.

Comments

[ckemmann]

Thanks for the quick response, but I'm trying to populate a combo box in a userform, not one on the spreadsheet. How can I adapt your code to work for a combo box in a userform?

[ckemmann]

Please see my 'response edit'. I've tried implementing your code and it's causing an error when I try to open the userform.

[ckemmann]

@Mike Laren, Gary's Student...either of you have an idea - I'm totally stumped here.

[Allstar]

@ckemmann , I think you have to reference the comboBox correctly... as here