Friday, March 17, 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 :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog