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.