Value and "Display Text" for IDataValidation objects?

[Elsa]

Hi! Is there a way to have both a value and a display text for a combo box created with an IDataValidation object?

What the user sees and select from a combo box is not what I will end up retrieving and storing in the database.

If not, is there another object that I can use to accomplish the same effect?

Thanks,

Elsa

[Administrator]

Hi Elsa, Sorry for the delay in getting you. I am afraid there is no support for creating Combobox in XlsIO. However ,XlsIO have provision for prompt box created with an IDataValidation object. Here is the code snippet for creating prompt box. //Data validation to list the values in the first cell. IDataValidation validation = sheet.Range["A1"].DataValidation; sheet.Range["A1"].Text = "Import from Database"; validation.ListOfValues = new string[] {"Yes","No"}; validation.PromptBoxText = "Import from Database"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; Retrieving the selected item in prompt box is same as retrieving cell contents. Unlike Combo box no cell link is needed in the prompt box so retrieving contents becomes simpler. Notes on the sample ------------------------------ 1) First click "CreateDatavalidation" button to create excel file having prompt box created with an IDataValidation object. 2)Save the opened file and close it. 3)Click the "Importing database" button and import the contents from the database. Please take a look at the attachment and let me know if it meets your requirements. Datavalidation.zip BestRegards, Bharath

[Elsa]

I added a "hidden" worksheet that contains the validation values. Alongside (in an adjacent column), I stored the "primary keys." Then, I used the DataRange property to point to the range in this other worksheet. You need to make sure that workbook.Allow3DRangesInDataValidation = true; for this strategy to work.

[Administrator]

Hi Elsa, Sorry for the delay in getting back to you. Using the following code

workbook.Allow3DRangesInDataValidation = true;

which will allow datavalidation having the datarange referring to the ranges in other sheets. Please take a look at the attached sample which will add the hidden sheet containing some datavalidation values and primary keys in the along side column which is referred by the data range for datavalidation used in another sheet. Sample : Allow3DRanges BestRegards, Bharath. >I added a "hidden" worksheet that contains the validation values. Alongside (in an adjacent column), I stored the "primary keys." Then, I used the DataRange property to point to the range in this other worksheet. You need to make sure that workbook.Allow3DRangesInDataValidation = true; for this strategy to work.