Excel VBA- Load data items into ComboBox

EXCEL VBA TO LOAD DATA IN A COMBOBOX

You can load data in a combo box at any sheet of workbook using VBA. 

1. Use Developer >> Insert >> Form Controls
2. Select Combo Box and then place this control at any suitable place on the worksheet.
3. You can change name of this form control in the Name Box.
4. Right Click the Control and click Assign Macro...
5. Assign Macro dialog box appears.
6. Click New button. 
7. Change event subroutine template appears in a new module.

The following code is used to populate data in the combo box named cboDropMonths assuming that data are in a row of the Sheet2 in Range F6 to BF6.



Sub pLoadData()

    Dim cboMonths As Object
    Dim varData As Variant
    Dim lngLastC As Long
    Dim lngC As Long
    
    Application.ScreenUpdating = False
    On Error GoTo ErrorHander
           
    Set cboMonths = Sheet1.DropDowns("cboDropMonths")
    varData = Sheet2.Range("F6:BF6")
    lngLastC = Sheet2.Range("F6:BF6").Columns.Count
    
    For lngC = LBound(varData) To lngLastC
            cboMonths.List(lngC) = varData(1, lngC)
''            Debug.Print cboMonths.List(lngC)
    Next

    Set cboMonths = Nothing

ErrorHander:

If Err.Number <> 0 Then
    MsgBox "ERROR NUMBER: " & Err.Number & vbCrLf & "ERROR DESCRIPTION " &     Err.Description
End If
Application.ScreenUpdating = True
End Sub