Seroius error while having combobox into each column's cells

Hot Topic

Correction to above post
Relationship to DSL tools
DTE2 interface returns 1.0 interfaces?
2005Beta2 cant launch Office project
clear tasklist
Visual Studio 2005 Word and Outlook 2003
Calculate Event and Changes in Range
Question about change in behavior
MSDN DLINQ Chat Transcript?
Reportviewer doesn't print first time I click print button

Visual Studio

Client Callback Address, wsDualHttpBinding and Windows Security
Basics
FlowDocument rendering questions
Setup project for VSTudio 2005 Addin. how to register in Addins folder?
Sum in Query Expression
Documenter
How to cast COMAddIn.Object in C#?
wsDualHttpBinding and TimeoutException
Payslip Printing
no dynamic image location in crystal reports for VS 2005?

Seroius error while having combobox into each column's cells

Hi all,

After binding my listobject/excelsheet with database I put following code to add combobox into each cell of the column.

Excel.Worksheet mainSheet = (Excel.Worksheet)Globals.ThisWorkbook.Sheets[1];

Excel.Range oRange = mainSheet.get_Range("C10", missing);

oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=C11:C150", " ");

oRange.Validation.IgnoreBlank = false;

oRange.Validation.InCellDropDown = true;

And here getting error for last line i.e.

oRange.Validation.InCellDropDown = true;

Here I have following problems:

1. The data appears into each combobox is depending only on the data into this column i.e. each combobox has all/duplicates data of that column. But my requirement is: this combobox should have all data corresponding to that column in database either that data present in excelsheet or not.

Is it possible to add some method or formula that grab all data from the respecting column into "formula1" & "formula2" argument of above Add method

Thank you

Answer this question

Seroius error while having combobox into each column's cells

  • Susan_MSFT

    Hi

    More details of the Validation.Add function can be found in the MSDN documentation at http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.validation.add(VS.80).aspx. However, I realised the interop documentation is not complete. If you look at the documentation provided with Excel installation itself for the Validation.Add function, you will find more details:

    Adds data validation to the specified range.

    expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)

    expression Required. An expression that returns a Validation object.

    Type Required XlDVType. The validation type.

    AlertStyle Optional Variant. The validation alert style. Can be one of the following XlDVAlertStyle constants: xlValidAlertInformation, xlValidAlertStop, or xlValidAlertWarning.

    Operator Optional Variant. The data validation operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual.

    Formula1 Optional Variant. The first part of the data validation equation.

    Formula2 Optional Variant. The second part of the data validation when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored).

    Remarks

    The Add method requires different arguments, depending on the validation type, as shown in the following table.

    Validation type Arguments
    xlValidateCustom Formula1 is required, Formula2 is ignored. Formula1 must contain an expression that evaluates to True when data entry is valid and False when data entry is invalid.
    xlInputOnly AlertStyle, Formula1, or Formula2 are used.
    xlValidateList Formula1 is required, Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list.
    xlValidateWholeNumber, xlValidateDate, xlValidateDecimal, xlValidateTextLength, or xlValidateTime One of either Formula1 or Formula2 must be specified, or both may be specified.

    As you will notice in Formula1 for xlValidateList you can specify a list of values separated with commas.

    So you can have some other function which gets the data from the database and populate the Excel Formula1 as comma-delimited list.

    oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "Australia, Canada, USA", " ");

    Hope that helps!

    -Nikhil



  • Mark_E475

    Hi Nikhil,

    Yeah, I have already followed the above document. And also got the idea how to put comma -delimited list as in following:

    oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "Australia, Canada, USA", " ");

    But the problem that I have:

    I have more that 500 elements into database and putting 500 elements separated by comma is not the good idea. Thats why I asked for if there any other possible way.

    Thank you

  • DLLnewbie

    Hi All,

    Still I didnt get any perfect solution here.

    Please assist me.

    Thank you

  • mma01

    Hi Prashant. I am also having the same problem. I am having list of 120 + countries which is comma sepertaed. When I am trying to feed it to methode

    oRng.Validation.Add(Excel.XlDVType.xlValidateList, Missing.Value, Excel.XlFormatConditionOperator.xlBetween, strResults[1], Missing.Value);

    where strResults[1] is the coma spertaed list of values. The end result is I am only getting first 30 valuse listed in my drop down list.

    Please help me out. If it is not possible in this way then suggest me some other way of dogin as you might have done.

    Thanks in Advance.


  • Seroius error while having combobox into each column's cells
Answer this question