CODEWITHSUNDEEP
c#excel
chhenning
chhenning

Reputation: 2077

How to make Excel cell a combobox using c#?

I have been searching the net but couldn't find a way to turn a cell into a combobox ( or dropdown as they seem to be called sometimes )? I'm using Microsoft.Office.Interop.Excel version 14.0 ( Runtime version 2.0.50727).

Upvotes: 2

Views: 6732

Answers (2)

chhenning
chhenning

Reputation: 2077

Here is the solution of what I tried to achieve:

Excel.Application App = null;
Excel.Workbook Book = null;

Excel.Worksheet Sheet = null;

object Missing = System.Reflection.Missing.Value;

try
{
    App = new Excel.Application();
    Book = App.Workbooks.Add();

    Sheet = (Excel.Worksheet) Book.Worksheets[1];

    Excel.Range Range = Sheet.get_Range("B2", "B2");

    Range.Validation.Add(Excel.XlDVType.xlValidateList
        , Excel.XlDVAlertStyle.xlValidAlertStop
        , Excel.XlFormatConditionOperator.xlBetween
        , "Item1,Item2,Item3"
        , Type.Missing);
    Range.Validation.InCellDropdown = true;
    Range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 217, 217, 0)); 

    App.Visible = true;
}
finally
{
    Base.ReleaseObject(Sheet);
    Base.ReleaseObject(Book);
    Base.ReleaseObject(App);
}

Upvotes: 5

JFish222
JFish222

Reputation: 1026

Well, an Excel hostable .Net combobox definitely exists. Is the example code in the post helpful?

http://msdn.microsoft.com/en-us/library/ee259140(v=vs.100).aspx

Private Sub ExcelRangeAddComboBox()

    Dim ComboBox1 As Microsoft.Office.Tools.Excel. _
        Controls.ComboBox = Me.Controls.AddComboBox( _
        Me.Range("A1", "B1"), "ComboBox1")
    ComboBox1.Items.Add("First Item")
    ComboBox1.Items.Add("Second Item")
    ComboBox1.SelectedIndex = 0

End Sub

If you'rr not familiar with developing add-ins, the following might help to get you started: http://msdn.microsoft.com/en-us/library/cc442981(v=vs.100).aspx

and the videos here were very helpful: http://msdn.microsoft.com/en-US/office/hh133459

Upvotes: 1

Related Questions