Tuesday, June 16, 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-I : 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

--------------------------------------------------------------------------------------------------------
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