StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>if you want to use a validation for that purpose the following method was written by me to add a Validation and a small Infobox that appears when the user clicks on the cell:</p> <pre><code>/// <summary> /// Adds a small Infobox and a Validation with restriction (only these values will be selectable) to the specified cell. /// </summary> /// <param name="worksheet">The excel-sheet</param> /// <param name="rowNr">1-based row index of the cell that will contain the validation</param> /// <param name="columnNr">1-based column index of the cell that will contain the validation</param> /// <param name="title">Title of the Infobox</param> /// <param name="message">Message in the Infobox</param> /// <param name="validationValues">List of available values for selection of the cell. No other value, than this list is allowed to be used.</param> /// <exception cref="Exception">Thrown, if an error occurs, or the worksheet was null.</exception> public static void AddDataValidation(Worksheet worksheet, int rowNr, int columnNr, string title, string message, List<string> validationValues) { //If the message-string is too long (more than 255 characters, prune it) if (message.Length > 255) message = message.Substring(0, 254); try { //The validation requires a ';'-separated list of values, that goes as the restrictions-parameter. //Fold the list, so you can add it as restriction. (Result is "Value1;Value2;Value3") //If you use another separation-character (e.g in US) change the ; appropriately (e.g. to the ,) string values = string.Join(";", validationValues); //Select the specified cell Range cell = worksheet.Cells[rowNr, columnNr]; //Delete any previous validation cell.Validation.Delete(); //Add the validation, that only allowes selection of provided values. cell.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, values, Type.Missing); cell.Validation.IgnoreBlank = true; //Optional put a message there cell.Validation.InputTitle = title; cell.Validation.InputMessage = message; } catch (Exception exception) { //This part should not be reached, but is used for stability-reasons throw new Exception(String.Format("Error when adding a Validation with restriction to the specified cell Row:{0}, Column:{1}, Message: {2}", rowNr, columnNr, message), exception); } } </code></pre> <p>If you don't need the Infobox, just leave out the parts where the variables title or message appear.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload