Tuesday, 16 June 2015

How to Get Combo box or Dropdown Index Value with Excel VBA

 Excel VBA to Get Combo box or Dropdown Index Value
Suppose we have a Form Control Combobox or Dropdown lis as follows :

Now we can select or change or get the above Dropdown list Index Value with VBA Macro as follows :

Sub Form_Ctrl_Cmbo_DropDown()

Dim Form_Obj As Object
  'Assinging the DropDown/Combobox(Form Control) to a Object Variable
Set Form_Obj = ActiveSheet.DropDowns("MyDropDown")

'To Display the Count of List Items
MsgBox Form_Obj.ListCount

'To Change the List Index Position
Form_Obj.ListIndex = 3

'Method-II : Loop Through and Display DropDown(Form Control) Each List Index Value
    For J = 1 To Form_Obj.ListCount
    MsgBox Form_Obj.List(J)

    Next J

'Method-II : To Display DropDown(Form Control) List Index and Value
MsgBox "The DropDown List Index Number : " & Form_Obj.ListIndex
MsgBox "The DropDown List Index Value : " & Form_Obj.List(Form_Obj.ListIndex)

'Method-III : To Display DropDown(Form Control) List Index and Value
With ActiveSheet.Shapes("MyDropDown").ControlFormat
MsgBox "Item Selected in DropDown = " & .List(.Value)
End With

End Sub
In the same way we can get the value of Active-X ComboBox values as follows :

Sub  ActivX_Cmbo_DropDown()

Dim OLE_Obj As Object

Set OLE_Obj = ActiveSheet.OLEObjects("MyCmb_Box").Object

'Counting the List of values in a Combo Box
MsgBox OLE_Obj.ListCount

'Passing the List Index Number to Combo Box
OLE_Obj.ListIndex = 4

'Displaying the List Index Value of Combo Box
MsgBox OLE_Obj.Value

Y = OLE_Obj.ListCount
MsgBox Y

'Looping through Combo box List items
For X = 0 To (Y - 1)

MsgBox OLE_Obj.List(X)

Next X

End Sub

Note :
Combobox List Index starts from 0, by defautlt, if you dont specify as Option Base 1

    [ BI-Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts