Hi
On CellDataChanged, I am checking a value if the value is not valid, I want the the same cell to be focused (as depicted in word file and as AddValidation function works). At the moment control moves on to next cell.
Also, sample code is attached. Kindly reply as soon as possible.
Hi,
Thanks for providing us details and sample project.
I think you may try to change your code a bit in CellDataChanged event handler for your need.
e.g..,
Private Sub grdDataEntry_CellDataChanged(ByVal sender As System.Object, ByVal e As Aspose.Grid.Desktop.CellEventArgs) Handles grdDataEntry.CellDataChanged
If m_blnImport = False Then
Dim lFocusedCell As Aspose.Grid.Desktop.GridCell
lFocusedCell = grdDataEntry.Worksheets(0).GetFocusedCell
If e.Cell.Value.GetType.Name = "String" OrElse frmCheckNumeric(e.Cell.Value) = False Then
MessageBox.Show("The value you entered is not valid.", "Validation", MessageBoxButtons.OK, MessageBoxIcon.Error)
If lFocusedCell IsNot Nothing Then
grdDataEntry.Worksheets(0).SetFocusedCell(lFocusedCell.Row - 1, lFocusedCell.Column)
End If
End If
End If
End Sub
If m_blnImport = False Then
Dim lFocusedCell As Aspose.Grid.Desktop.GridCell
lFocusedCell = grdDataEntry.Worksheets(0).GetFocusedCell
If e.Cell.Value.GetType.Name = "String" OrElse frmCheckNumeric(e.Cell.Value) = False Then
MessageBox.Show("The value you entered is not valid.", "Validation", MessageBoxButtons.OK, MessageBoxIcon.Error)
If lFocusedCell IsNot Nothing Then
grdDataEntry.Worksheets(0).SetFocusedCell(lFocusedCell.Row - 1, lFocusedCell.Column)
End If
End If
End If
End Sub
Thank you.
Hi,
Well, we think it is better to use Custom Validation instead of handling CellDataChanged event.
Please try:
1) In the Validation_Load sub, you may add a few lines, e.g..,
grdDataEntry.Worksheets(0).Columns(8).AddValidatoin(New CustomValidation())
grdDataEntry.Worksheets(0).Columns(8).Validation.CustomMsgTitle = "CustomMsgTitle"
grdDataEntry.Worksheets(0).Columns(8).Validation.CustomMsgForStopEdit = "CustomMsgEdit"
2) Remove the grdDataEntry_CellDataChanged sub and frmCheckNumeric function from the form class.
3) Lastly, add a CustomValidation class and create two method in it for your need, e.g..,
Public Class CustomValidation
Implements Aspose.Grid.Desktop.ICustomValidation
Public Function Validate(ByVal worksheet As Aspose.Grid.Desktop.Worksheet, ByVal row As Integer, ByVal col As Integer, ByVal value As Object) As Boolean Implements Aspose.Grid.Desktop.ICustomValidation.Validate
Dim lFocusedCell As Aspose.Grid.Desktop.GridCell
lFocusedCell = worksheet.GetFocusedCell
If value.GetType.Name = "String" OrElse frmCheckNumeric(value) = False Then
Return False
End If
End Function
Public Function frmCheckNumeric(ByVal val As String) As Boolean
Try
If Not IsNumeric(Trim(val)) Then Return False
If InStr(val, ".") > 0 Then Return False
Return True
Catch ex As Exception
Throw ex
End Try
End Function
End Class
Thank you.
Hi Amjad
I created a custom validation class with the following code.
Public Class CustomValidation
Implements Aspose.Grid.Desktop.ICustomValidation
Public Function Validate(ByVal worksheet As Aspose.Grid.Desktop.Worksheet, ByVal row As Integer, ByVal col As Integer, ByVal value As Object) As Boolean Implements Aspose.Grid.Desktop.ICustomValidation.Validate
Dim lFocusedCell As Aspose.Grid.Desktop.GridCell
lFocusedCell = worksheet.GetFocusedCell
If value.GetType.Name = “String” OrElse frmCheckNumeric(value) = False Then
Return False
End If
End Function
Public Function frmCheckNumeric(ByVal val As String) As Boolean
Try
If Not IsNumeric(Trim(val)) Then Return False
If InStr(val, “.”) > 0 Then Return False
Return True
Catch ex As Exception
Throw ex
End Try
End Function
I want to impose following two validations on a column. Please suggest how can we give two validations to a column OR how can I incorporate my first validation in the Custom class? Any help, will be highly appreciated.
grdDataEntry.Worksheets(0).Columns(lintColCounter).AddValidation(False, “\d{1,13}”)
grdDataEntry.Worksheets(0).Columns(lintColCounter).AddValidatoin(New CustomValidation())
Thanks
Sukhminder Singh
Hi Sukhminder,
Well, adding two validations on a column is not supported in Aspose.Grid.Desktop control. Currently, you can only use custom validation to implement more complex validations (including regular expressions).
Please try the following code if it fits your need.
Sample code:
Imports System.Text.RegularExpressions
Public Class CustomValidation
Implements Aspose.Grid.Desktop.ICustomValidation
Public Function Validate(ByVal worksheet As Aspose.Grid.Desktop.Worksheet, ByVal row As Integer, ByVal col As Integer, ByVal value As Object) As Boolean Implements Aspose.Grid.Desktop.ICustomValidation.Validate
Dim lFocusedCell As Aspose.Grid.Desktop.GridCell
Dim regex As Regex = New Regex("\d{1,13}")
lFocusedCell = worksheet.GetFocusedCell
If (regex.IsMatch(value.ToString())) Then
If value.GetType.Name = "String" OrElse frmCheckNumeric(value) = False Then
Return False
End If
Return True
End If
Return False
End Function
Public Function frmCheckNumeric(ByVal val As String) As Boolean
Try
If Not IsNumeric(Trim(val)) Then Return False
If InStr(val, ".") > 0 Then Return False
Return True
Catch ex As Exception
Throw ex
End Try
End Function
End Class
Thank you.
Hi Amjad
I used the code you sent, It helped partially. Now, it’s not allowing me to give formulas in the cell (Please see the in the attached code), I need to know how can I check if the given value is a valid formula or not?
Thanks
Sukhminder Singh
Hi Sukhminder,
We will check if it can be implemented for your need.
Thank you.
Hi,
Generally, we check a formula if it starts with ‘=’ character. So, you may add the following code before applying the regular expression validation, it will work fine for your need:
If Not value Is Nothing Then
Dim str As String = value.ToString
If str.Length > 1 And str(0) = "=" Then
Return True
End If
End If
Thank you.