title: How to Populate a Combobox
body: <p>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.</p>
  
  <p>I've looked through several questions both here and elsewhere when making this question, but I haven't found anything that worked.</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/15414619/populate-combobox-in-vba-with-array-elements">populate combobox in VBA with array elements</a></li>
  <li><a href="https://stackoverflow.com/questions/14392122/how-do-i-populate-a-combo-box-from-a-column-in-my-excel-spread-sheet">How do I populate a combo box from a column in my excel spread sheet?</a></li>
  <li><a href="http://www.techrepublic.com/blog/microsoft-office/populate-a-userform-combo-box-in-excel-with-a-dynamic-list/" rel="nofollow noreferrer">http://www.techrepublic.com/blog/microsoft-office/populate-a-userform-combo-box-in-excel-with-a-dynamic-list/</a></li>
  </ul>
  
  <p>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.</p>
  
  <p>Attempt #1 This involved creating a dynamic range:</p>
  
  <pre><code>=OFFSET(PC_DataSheet!$A$2,0,0, COUNTA(PC_DataSheet!$A$1:$A$65536)-1,1)</code></pre>
  
  <pre><code>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
  </code></pre>
  
  <p>Attempt #2</p>
  
  <pre><code>Private Sub UserForm1_Initialize()
  
      Dim arr() As Variant
  
      arr = Worksheets("Sheet1").Range("C2:" &amp; lrow).Value
      PC_ListComboBox.List = arr
  
  End Sub
  </code></pre>
  
  <p>Attempt #3</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any help on this would be really appreciated!</p>
  
  <hr>
  
  <p><strong>EDIT:</strong> I've tried inserting the code suggested by <em>Gary's Student</em> into my UserForm_Initialize() Sub, but when I try to open the userform I get the following error message:</p>
  
  <blockquote>
    <p>Run-time error '9': Subscript out of range</p>
  </blockquote>
  
  <p>When I click debug, it highlights this code:</p>
  
  <pre><code>'Opens PC UserForm when pressed.
  Private Sub AddPCButton_Click()
  
      UserForm.Show 'This line is the line highlighted by the debugger.
  
  End Sub
  </code></pre>
  
  <p>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.</p>
  
  <pre><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
  </code></pre>
  
  <p>This is including the suggested code:</p>
  
  <pre><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
  </code></pre>