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 

--------------------------------------------------------------------------------------------------------