excel validation rule using vb.net using spreadsheetgear

Question

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.

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. msdn.microsoft.com/en-us/library/office/ff840715.aspx — Visual Vincent
– Visual Vincent, Commented Sep 11, 2015 at 11:05
sorry actually this is related to spreadsheetgear dll which is used to automate excel using vb.net — Ramesh
– Ramesh, Commented Sep 11, 2015 at 13:24

Tim Andersen · Accepted Answer · 2015-09-28 14:23:30Z

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)

edited Sep 28, 2015 at 14:23

answered Sep 11, 2015 at 14:30

Tim Andersen

3,1841 gold badge17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ramesh Over a year ago

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

Tim Andersen Over a year ago

I've updated my question to accommodate your question about validating without an actual dropdown list appearing (it's a bit more involved).

Ramesh Over a year ago

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.

Tim Andersen Over a year ago

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: stackoverflow.com/a/16098118/233365

Collectives™ on Stack Overflow

excel validation rule using vb.net using spreadsheetgear

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related