How to create validation range from value that input by user?

Question

I want to create a validation range in cell "A1". This validation allow user to input value from 1 to x, while x refer to value user input in "B1". For example, if user input value 100 in "B1" then we can input only 1 to 100 in cell "A1".

I want to know how to do this in C#?

Answer 1

Assuming you have interop, it would be like the below. Note xlValidateWholeNumber, that can be changed to xlValidateDecimal (and the subsequent Formula1 below would need to be a decimal) for decimals).

private void SetValidationBetweenNumbers() 
{ 

    Microsoft.Office.Tools.Excel.NamedRange cellThatNeedsValidating = 
        this.Controls.AddNamedRange(this.Range[""A1"", missing], 
        "cellThatNeedsValidating"); 

    cellThatNeedsValidating.Validation.Add( 
        Excel.XlDVType.xlValidateWholeNumber, 
        Excel.XlDVAlertStyle.xlValidAlertStop, 
        Excel.XlFormatConditionOperator.xlBetween, "1", "=B1"); 
}