none
How to restrict values of Data validation list changing to date RRS feed

  • Question

  • Hi,

    We are creating a data validation list from C# code. This list will contain Month with year. How can we restrict excel from converting this list from changing to date format?

    Below is the code: 

    range.Validation.Add(XlDVType.xlValidateList,
                                   XlDVAlertStyle.xlValidAlertInformation,
                                   XlFormatConditionOperator.xlBetween,
                                   flatList,
                                   Type.Missing);
    range.Validation.IgnoreBlank = false;

    range.Validation.InCellDropdown = true;

    flatList will have values "June 2016, July 2016, August 2016". Excel converts the list to "Jun-2016, Jul-2016, Aug-2016". We want that to be avoided and the list should remain as text. 

    Please advice.

    Friday, February 3, 2017 8:28 AM

All replies

  • Hi,

    You could change the NumberFormat of the list range into

      rng.NumberFormat = "mmmm, yyyy";

    Then it would show like "June 2016, July 2016, August 2016".

    Regards.

    Celeste


    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, February 6, 2017 5:47 AM
    Moderator
  • Hi Celeste,

    This solution dint work. The list is still converted to date format ( Jul-2016,Aug-2016).

    Monday, February 6, 2017 6:45 AM
  • Hi,

    What is your flatList? A string array or string referring to sheet range?

    Do you want the value in the list showing formatting in "mmmm, yyyy"?

    If flatlist is a string array like  "June 2016, July 2016, August 2016", I think we could not let it show in correct format in the list. We could only set the selected value for the range into "mmmm, yyyy".

    You could input the array in the excel cells and set it as the list source of cell validation. 

    E.g.

       Excel.Range range = Globals.ThisAddIn.Application.ActiveSheet.Cells(1, 1);
                Excel.Range flatList = Globals.ThisAddIn.Application.ActiveSheet.Range["C1:C2"];
                range.Validation.Add(Excel.XlDVType.xlValidateList,
                   Excel.XlDVAlertStyle.xlValidAlertInformation,
                   Excel.XlFormatConditionOperator.xlBetween,
                   "="+flatList.Address);
                range.Validation.IgnoreBlank = false;
                range.Validation.InCellDropdown = true;
                flatList.NumberFormat = "mmmm, yyyy";
                range.NumberFormat = "mmmm, yyyy";
     

    Regards,

    Celeste

     


    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, February 6, 2017 7:03 AM
    Moderator
  • Thanks for the Quick response.

    Flatlist is the array of string which we get from the Database.

    Writing flatlist to range in sheet might cause us issues because each time we load this data we need to create the sheet with range to reference it.

    Monday, February 6, 2017 7:54 AM
  • Hi,

    If the workaround is not suitable, there is no other method to let it show custom format in the list.

    Regards,

    Celeste


    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.

    Tuesday, February 7, 2017 4:25 AM
    Moderator
  • Thanks Celeste.
    Tuesday, February 14, 2017 3:10 AM