Validation

Posted by: lg_sony_27 on 8 September 2017, 1:15 pm EST

  • Posted 8 September 2017, 1:15 pm EST

    Hello!


    How can I validate if the user input in the second column is greater than the value of the first column. 
    I am trying to imitate the Data Validation in excel. If input is greater then it will display message box.


    thanks!

  • Replied 8 September 2017, 1:15 pm EST

    This should give you an idea.  Keep in mind validation will only occur when you click off the cell you are editing...

    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell
    Dim r As Integer
    Dim v As Object
    Dim v2 As Object
    For r = 0 To 9
    v = FpSpread1.ActiveSheet.GetValue(r, 0)
    v2 = FpSpread1.ActiveSheet.GetValue(e.Row, e.Column)
    If v2 > v Then
    FpSpread1.ActiveSheet.Cells(e.Row, e.Column).ForeColor = Color.Red
    End If
    Next
    End Sub
  • Replied 8 September 2017, 1:15 pm EST

    Hello! Thanks. I will try that and be back later for any feedback.
  • Replied 8 September 2017, 1:15 pm EST

    I would suggest either using the LeaveCell event and checking the value of the cell you have just left or  the Changed event of the DataModel.
  • Replied 8 September 2017, 1:15 pm EST

    I cant make it work. I would like to change the font color too depending on the result when I validate the cell. How do i do it? any sample code?

  • Replied 8 September 2017, 1:15 pm EST

    What if I would like the whole column to be affected by the leavecell event.


    On leavecell the following should be checked:


    1. check if user input is greater than the value in another column (i.e. A1 has 10 and user input in B1 is 11). then it will not proceed instead a messagebox will be displayed informing the user why.


    2. check if the computer value is greater than or equal to 74.5 ( if less font color is red


    Note: criteria #2 is another column (C1)


     

  • Replied 8 September 2017, 1:15 pm EST

    You need to remove FpSpread1.ActiveSheet.RowCount, 1 and change the 0 to a -1...

    Dim styleHot As New FarPoint.Win.Spread.NamedStyle
            styleHot.ForeColor = Color.Red

            If TypeOf FpSpread1.ActiveSheet.Models.Style Is FarPoint.Win.Spread.IConditionalFormatSupport Then

                CType(FpSpread1.ActiveSheet.Models.Style, FarPoint.Win.Spread.IConditionalFormatSupport).SetConditionalFormat(-1, 0, styleHot, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "74.50")
            End If
  • Replied 8 September 2017, 1:15 pm EST

    Hello,


    1) In the LeaveCell event, the Column parameter is passed into the event.


      If e.Column=2 Then
         'Do Code
      End If


    2) Here are the changes needed for the code.

     
           'put cell forecolor


    Dim styleHot As New FarPoint.Win.Spread.NamedStyle
    styleHot.ForeColor = Color.Red

    If TypeOf FpSpread1.ActiveSheet.Models.Style Is FarPoint.Win.Spread.IConditionalFormatSupport Then

    CType
    (FpSpread1.ActiveSheet.Models.Style, FarPoint.Win.Spread.IConditionalFormatSupport).SetConditionalFormat(0, 11, FpSpread1.ActiveSheet.RowCount, 1), styleHot, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "74.50")
    End If
     
  • Replied 8 September 2017, 1:15 pm EST

    Hello,


    1) In the LeaveCell event you can check the Column parameter for the one parameter you want to check. If you do not want focus to leave that cell, you can set the Cancel parameter in the event to True.


    2) You can use the SetConditionalFormat method to have this happen automatically.

  • Replied 8 September 2017, 1:15 pm EST

    scotts:

    Hello,


    1) In the LeaveCell event you can check the Column parameter for the one parameter you want to check. If you do not want focus to leave that cell, you can set the Cancel parameter in the event to True.


    2) You can use the SetConditionalFormat method to have this happen automatically.



     


    1. where do i check the column parameter?


    2. SetConditionalFormat will affect a cell. What if I just want to change the color of the text?

  • Replied 8 September 2017, 1:15 pm EST

    im getting blue underline in this line of code


    CType(FpSpread1.ActiveSheet.Models.Style, FarPoint.Win.Spread.IConditionalFormatSupport).SetConditionalFormat(0, 11, FpSpread1.ActiveSheet.RowCount, 1), styleHot, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "74.50")



    with this Error  Overload resolution failed because no accessible 'SetConditionalFormat' accepts this number of arguments.

  • Replied 8 September 2017, 1:15 pm EST

    I have this so far in SetconditionalFormat

        Private Sub conditionalFormat()
    'put cell backcolor

    Dim styleHot As New FarPoint.Win.Spread.NamedStyle
    styleHot.BackColor = Color.Red

    If TypeOf FpSpread1.ActiveSheet.Models.Style Is FarPoint.Win.Spread.IConditionalFormatSupport Then

    CType
    (FpSpread1.ActiveSheet.Models.Style, FarPoint.Win.Spread.IConditionalFormatSupport).SetConditionalFormat(0, 11, styleCold, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "74.50")
    End If
    End Sub

     How do I make that available for the whole column and not by cell and the color red is the text not as background of the cell?


    I am not still able to find the column parameter you mentioned.


    Thanks!


     

  • Replied 8 September 2017, 1:15 pm EST

    Thanks! Great! I t works now!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels