Friday, 17 March 2017

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 :



No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts