none
What is the proper way to write to a spreadsheet cell that holds a drop down menu (ComboBox)? RRS feed

  • Question

  • Until recently I didn't know that Excel allows the spreadsheet designer to create a drop-down menu in a cell. For instance, if the end user clicks on that cell the values "January", "February", ..., "December" are shown and the user is limited to typing a month name or removing any content.

    Programmatically, which is the recommended way to fill one such cell? I have programmed other similar environments in the past and an integer -not a string- is used in order to select a value.

    TIA



    Saturday, June 23, 2018 11:41 PM

Answers

  • Hello Travis Banger,

    You could still use the code to write value to the cell like

    activeCell.Value = "April";

    However, i think it is not possible to enter value via index. There is no property/method of Validation Object  could do this.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Travis Banger Monday, June 25, 2018 7:38 PM
    Monday, June 25, 2018 9:42 AM
  • Terry: The AddIn is working fine now. I am removing a formula that lives in that cell and stamping the LHS of that formula.

    Thx!

    • Marked as answer by Travis Banger Tuesday, June 26, 2018 2:26 PM
    Monday, June 25, 2018 7:40 PM

All replies

  • Hello Travis Banger,

    According to your description, I think what you want to set is the data validation for the cell. 

    Programmatically, we could use Validation.Add to add a data validation to a cell.

    Here is the simple code.

     Excel.Application xlApplication = Globals.ThisAddIn.Application;
                Excel.Range activeCell = xlApplication.ActiveCell;
                activeCell.Validation.Delete();
                activeCell.Validation.Add(
                    Excel.XlDVType.xlValidateList,
                    Excel.XlDVAlertStyle.xlValidAlertStop,
                    Excel.XlFormatConditionOperator.xlBetween,
                    "January,February,March,April,May");

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 25, 2018 2:11 AM
  • Terry: Your suggestion is very helpful. However, the validation in that cell was done a long time ago and I am not trying to replace it. I am simply wondering if my code should perform something like:

    cell.content.text = "Month of March";  // the real content has several words and numbers

    or whether it is possible to do something like this:

    cell.content.index = 3    // March is the 3rd. month.

    I guess I will try stamping the long, more complex string.

    Thx

    ps: The above statements are not supposed to be syntactically correct.

    Monday, June 25, 2018 3:44 AM
  • Hello Travis Banger,

    You could still use the code to write value to the cell like

    activeCell.Value = "April";

    However, i think it is not possible to enter value via index. There is no property/method of Validation Object  could do this.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Travis Banger Monday, June 25, 2018 7:38 PM
    Monday, June 25, 2018 9:42 AM
  • Terry: The AddIn is working fine now. I am removing a formula that lives in that cell and stamping the LHS of that formula.

    Thx!

    • Marked as answer by Travis Banger Tuesday, June 26, 2018 2:26 PM
    Monday, June 25, 2018 7:40 PM