check value entered

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

  • Posted 8 September 2017, 1:24 pm EST

    hi! How do i check the value entered by the user if it is greater than the value in another column. if it greater then prompt the user.
  • Replied 8 September 2017, 1:24 pm EST

    I tried this code but it seems that I can still type values greater than the value in another column.


    This is what I want to achieved, let's say I have 20 columns. Column A contain for example 100. In column B the user will type values. the user should not be able to type more than 100. If the user will try to type more 100 then a prompt will display telling the user that it's not allowed user cannot go on unless the values is less than or equal 100. The same thing will also be done with the rest of the column.


    scotts:


    Hello,


    You can add code like this to the LeaveCell event of Spread.


    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell


    If e.Column = 2 Then


    'Leaving column 2 so check to make sure it is valid data in the row


    FpSpread1.StopCellEditing()


    If FpSpread1.Sheets(0).GetValue(e.Row, 1) = "Stop" Then


    e.Cancel = True


    End If


    End If


    End Sub

  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    You can add code like this to the LeaveCell event of Spread.


    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell


    If e.Column = 2 Then


    'Leaving column 2 so check to make sure it is valid data in the row


    FpSpread1.StopCellEditing()


    If FpSpread1.Sheets(0).GetValue(e.Row, 1) = "Stop" Then


    e.Cancel = True


    End If


    End If


    End Sub

  • Replied 8 September 2017, 1:24 pm EST

    any code snippet here for me to start with please?
  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    You can map the Change event and check the e.Row and e.Column parameters to be notified of the cell that had been changed. Then, you can query it's Value property and the Value property of other cells to find out if it is a valid entry. If you do not want the user to leave this cell until it is valid, you can map the LeaveCell event instead and move your code into that event. You can set the e.Cancel parameter to True to keep the focus on the cell.

  • Replied 8 September 2017, 1:24 pm EST

    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell
    Dim c As Object = FpSpread1.ActiveSheet.GetValue(e.Row, e.Column)

    Dim r As Object = FpSpread1.ActiveSheet.GetValue(3, 3)

    If c > r Then
    MsgBox("Value is greater")
    e.Cancel = True
    End If
    End Sub
  • Replied 8 September 2017, 1:24 pm EST

    Then just use the code I gave you earlier.  You are going to have to compare the value entered in the cell to SOMETHING. 
  • Replied 8 September 2017, 1:24 pm EST

    The code I gave you will do this but it will not prevent a user from typing in more than 100.  You would have to set up a celltype that has a MaximumValue property and set it to 100.

     

  • Replied 8 September 2017, 1:24 pm EST

    Im sorry, wrong choice of word, the user can still type more than the value in column A but will prompt the user that what he/she entered is more than what is required. I would like simulate the data validation in excel.


    By the way, in your code how do i make it work for the whole column and the rest of the columns?

  • Replied 8 September 2017, 1:24 pm EST

    The code I gave you will work for all columns...

    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell
    Dim c As Object = FpSpread1.ActiveSheet.GetValue(e.Row, e.Column)

    If e.Column <> 0 Then
    If c > 100 Then
    MsgBox("Value is greater")
    e.Cancel = True

    End If
    End If

    End Sub

  • Replied 8 September 2017, 1:24 pm EST

    My columns are already numbercelltype. How do I modify the MaximumValue? Maximumvalue differs for every cell.

  • Replied 8 September 2017, 1:24 pm EST

    If you're talking just numeric values then the best thing to do would be to have the second column as a NumberCellType column and modify its MaximumValue property whenever the cell in the column preceding it changes.
  • Replied 8 September 2017, 1:24 pm EST

    what happen to this line in your last post?  Dim r As Object = FpSpread1.ActiveSheet.GetValue(3, 3) and by the way, the value is not always 100, it varies from column to column.

  • Replied 8 September 2017, 1:24 pm EST

    Hello,


    If you are going to have possible different MaximumValue for each row, you would need to create a different NumberCellType object (with the correct MaximumValue property set) and assign it to the individual cell.


    Or you can use code like what was posted before to check the value after it is entered and see if it is too large. If it is then you can cancel the LeaveCell event and throw an error message to your application if you need.

Need extra support?

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

Learn More

Forum Channels