How to get the value of selected item in a xlDropDown which is added programmatically to a Excel sheet cell

Question

The following code shows the way i created the drop downs programmatically. It works fine. But now I need to get value of a specific dropdown of a cell.

Microsoft.Office.Interop.Excel.DropDowns xlDropDowns;
Microsoft.Office.Interop.Excel.DropDown xlDropDown;
xlDropDowns = ((Microsoft.Office.Interop.Excel.DropDowns)(sheet.DropDowns(Type.Missing)));
xlDropDown = xlDropDowns.Add((double)rag.Left, (double)rag.Top, (double)rag.Width, double)rag.Height, true);
var DropDownList = {"aaaa","bbbb","cccc","dddd"};

int x = 0;
foreach (var item in DropDownList)
{
    x++;
    xlDropDown.AddItem(item);
}

This is how i tried to get the xlDropDown value. currentCell is the cell where i have the drop down

ColumnVal = currentCell.Text; // This didnt give any output

OR

var dd = (Microsoft.Office.Interop.Excel.DropDown)currentCell.DropDowns(Type.Missing);

I know the 2nd one is wrong, because the cell range and drop down are 2 different things. But I tried all the options, still couldnt find any solution. Someone please help me

More clearly, I want to access a specific cell(currentCell), and the xldropdown it contains and then get value from it

Answer 1

First you would need a reference to the drop down you've just added:

*Assuming there's only one drop down, the below would do

xlDropDown = ((Excel.DropDown)(xlDropDowns.Item(1)));

then you need to access the .get_List() property of the Excel.DropDown while making sure that something has been selected.

Example:

if (xlDropDown.Value > 0)
{
    sht.get_Range("A1").Value = xlDropDown.get_List(xlDropDown.Value);
}
else
{
    throw new Exception("Nothing was selected yet");
}

---

Identifying the dropdowns:

You could a for each loop on the xlDropDowns collection and grab the .Name and .ListIndex of each xlDropDown?

foreach (Excel.DropDown xlDD in xlDropDowns)
{
    MessageBox.Show(xlDD.Name + ", " + xlDD.ListIndex);
}