We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Desktop grid - Validation Issue (Restrict Focus movement)

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

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 ).

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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.