1

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.

Ramesh
  • 13
  • 6
  • Perhaps you could use the `List` validation? I don't know anything about working with Excel in Visual Basic, but "`Value must be present in a specified list`" sounds good to me. https://msdn.microsoft.com/en-us/library/office/ff840715.aspx – Visual Vincent Sep 11 '15 at 11:05
  • sorry actually this is related to spreadsheetgear dll which is used to automate excel using vb.net – Ramesh Sep 11 '15 at 13:24
  • That still includes the `List` validation AFAIK. – Visual Vincent Sep 12 '15 at 08:15

1 Answers1

0

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)
Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • sorry Tim, in continuation for this answer, can I validate X without showing it as list, instead I want to enter X or x and it should validate(I don't want that list selection to appear), How could I achieve that . Thanks – Ramesh Sep 28 '15 at 07:01
  • I've updated my question to accommodate your question about validating without an actual dropdown list appearing (it's a bit more involved). – Tim Andersen Sep 28 '15 at 14:24
  • One more please, can we make a cell to allow a date value only with in some range , say for example 01/1900 - 01/2020 , so here if he tries to enter some junk date like 05/3452 it should throw an alert."Enter valid date"....Please help on this as well. – Ramesh Sep 29 '15 at 14:08
  • At this point, perhaps you should just open a new question. That said, the way this is done in SpreadsheetGear is not very different from how it's done in Excel. You might look into an answer such as this one to figure it out on your own: http://stackoverflow.com/a/16098118/233365 – Tim Andersen Sep 29 '15 at 14:35