How to add an Excel Range to an ActiveX ComboBox List on DropButtonClick

VBA Macro to add an Excel Range to an ActiveX ComboBox List on DropButtonClick

Private Sub Cmb_FiscQtr_DropButtonClick()

    Dim WS As Worksheet

    Dim r As Long

    Dim n As Long

    Dim x As Long

    Dim y As Long

    

    Dim OLE_Obj As Object    

    Dim FQ_Items() As Variant

    Set WS = Worksheets("Test")

    Set OLE_Obj = WS.OLEObjects("Cmb_FiscQtr").Object

    On Error Resume Next

'The Range where we have the List      

    x = WS.Range("F65536").End(xlUp).Row

'Re-sizing the Array Size  

    ReDim FQ_Items(1 To x - 9)

'Adding Fiscal Quarters from an Excel Range to the Combobox Dropdown List        

    FQ_Items(1) = "(All)"

    n = 2

    For r = 11 To x

        FQ_Items(n) = WS.Cells(r, 6).Value

        n = n + 1

    Next r

    Cmb_FiscQtr.List = FQ_Items

    

 'Displaying the Combox Box List Items

    y = OLE_Obj.ListCount

    For x = 0 To (y - 1)

    OLE_Obj.ListIndex = x

    MsgBox OLE_Obj.List(x)

    Next x

    

'Erasing the Items from Arrary

    Erase FQ_Items

    Set WS = Nothing

       

End Sub

Example :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------