CODEWITHSUNDEEP
vb.netexcelvalidationspreadsheetgear
Ramesh
Ramesh

Reputation: 13

excel validation rule using vb.net using spreadsheetgear

I want to add validation rule to excel dynamically using VB.Net , I am able to add date validation to a cell as below using spreadsheetgear

worksheet.Range(DateRange).Validation.Add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Greater, dt, Nothing)
worksheet.Range(DateRange).NumberFormat = "mm/dd/yyyy"

In the same way how to make a validation rule for the cell to accept only single character both "x" and "X".thanks in advance.

Upvotes: 1

Views: 586

Answers (1)

Tim Andersen
Tim Andersen

Reputation: 3184

Restrict input to "x,X" using a ValidationType.List

You can use the ValidationType.List enum option to accomplish this. Example:

worksheet.Range(SomeRange).Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Default, "x,X", Nothing)

Restrict input to "x,X" WITHOUT using a ValidationType.List

The ValidationType.List enum option will always display a dropdown when the cell is selected. If you don't want this dropdown to appear, you'll need to take an alternative approach by providing a custom formula to do the validation (ValidationType.Custom), which may require a bit of effort to work correctly because you are responsible for validating input for each cell in SomeRange.

Below is a general approach for going about this (note the actual formula I used is just one way to go about accomplishing your task...any number of other formulas could be used to do the same thing):

' Get IRange representing your SomeRange string (say, "A1:B5").  
Dim MyRange As IRange = worksheet.Range(SomeRange)

' Get (relative-referenced) address of top-left cell for SomeRange (A1 in this case).  
' We need to use this cell address in the formula to validate input.  This approach
' will still work fine if "SomeRange" is a single cell instead of a multi-cell reference.
Dim TopLeftCell As String = MyRange(0, 0).GetAddress(False, False,
    ReferenceStyle.A1.A1, False, Nothing)

' Put together a "validation formula" (i.e., =LOWER(A1)="x").
Dim ValidationFormula = String.Format("=LOWER({0})=""x""", TopLeftCell)

' Create validation for all cells in MyRange (A1:B5 in this example), using the 
' ValidationFormula as the basis for this validation.
MyRange.Validation.Add(ValidationType.Custom, ValidationAlertStyle.Stop, 
    ValidationOperator.Default, ValidationFormula, Nothing)

Upvotes: 0

Related Questions