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 :


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

1 comment:

  1. Prety portion of content. I just stumbvled upon your site and
    in accession capital to axsert that I gget actually loved account your
    weblog posts. Anyway I'll be subscribing on your augment or even I fulfillment you get entry to consistently fast.

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts