Solved

Comparing the values in 2 cells in a MSFLEXgrid control..

Posted on 2004-10-29
219 Views
Last Modified: 2012-05-05
Hi ,

I have a flex grid control with 4 columns and I have to check wheather the value entered in 2nd column is greater than the value entered in the 3rd column of each row.

I am writing the code on the leavecell event but the code fires everytime I press a key. I want to execute it only when I leave the cell and move to another one or move the focus to another control.

My code is


Private Sub MSFlexGrid1_LeaveCell(index As Integer)
If MSFlexGrid1(index).Col = 3 Then
    If MSFlexGrid1(index).TextMatrix(MSFlexGrid1(index).Row, 2) > MSFlexGrid1(index).TextMatrix(MSFlexGrid1(index).Row, 3) Then
        MsgBox "Wrong Value"
    End If
End If
End Sub

Thanks,
0
Question by:swami_varahagir
    11 Comments
     
    LVL 6

    Expert Comment

    by:PePi
    how are you entering data into the flexgrid? MSFlexGrid has no Cell editing feature.

    anyways, try putting your code in the RowColChange event. I am assuming that initally, column 2 or 3 has the focus. you may also have to code the LostFocus event

    let me know how it turned out


    0
     

    Author Comment

    by:swami_varahagir
    In RowColchange event also it remains the same.

    I am using Keypress and KeyUp events to put the value I typed in into the cells. It is not an edit box but made it a kind of.

    Let me hv some guide lines how to approach it.

    Thanks,
    0
     
    LVL 6

    Expert Comment

    by:PePi
    i assume that you are using up, down, left & right arrow to move from one cell to another. can you show me your key_press and key_up codes so that i can have a better understanding on what you are doing?
    0
     

    Author Comment

    by:swami_varahagir
    These are the events I have written for the flex grid control.

    Thanks,


    Private Sub MSFlexGrid1_KeyPress(index As Integer, KeyAscii As Integer)
        Dim oldCol
        oldCol = MSFlexGrid1(index).Col
        MSFlexGrid1(index).Col = 0
           MSFlexGrid1(index).Col = oldCol
            If MSFlexGrid1(index).Col = 0 Or MSFlexGrid1(index).Col = 1 Then
                KeyAscii = 0
            End If
            If (KeyAscii = 8) Then
                If (Len(MSFlexGrid1(index).Text) > 0) Then
                    MSFlexGrid1(index).Text = Left(MSFlexGrid1(index).Text, Len(MSFlexGrid1(index).Text) - 1)
                Else
                    KeyAscii = 0
                End If
            ElseIf Len(MSFlexGrid1(index).Text) < 5 Then
            Select Case KeyAscii
                Case 48 To 57:
                    MSFlexGrid1(index).Text = MSFlexGrid1(index).Text + Chr(KeyAscii)
                Case Else
                    MsgBox "Only Numbers allowed"
            End Select
            End If
    End Sub


    Private Sub MSFlexGrid1_KeyUp(index As Integer, KeyCode As Integer, Shift As Integer)
        If Not (MSFlexGrid1(index).Col = 0 Or MSFlexGrid1(index).Col = 1) Then
            If KeyCode = 46 Then
                MSFlexGrid1(index).Text = ""
            End If
        End If
    End Sub


    Private Sub MSFlexGrid1_RowColChange(index As Integer)
    If MSFlexGrid1(index).Col = 3 Then
        If MSFlexGrid1(index).TextMatrix(MSFlexGrid1(index).Row, 2) > MSFlexGrid1(index).TextMatrix(MSFlexGrid1(index).Row, 3) Then
            MsgBox "Wrong Value"
        End If
    End If
    End Sub
    0
     
    LVL 6

    Expert Comment

    by:PePi
    try this:

    Private Sub flexgrid_KeyPress(KeyAscii As Integer)
        If flexgrid.Col = 0 Or flexgrid.Col = 1 Then
            KeyAscii = 0
        End If
       
        Select Case KeyAscii
            Case 8
                If (Len(flexgrid.Text) > 0) Then
                    flexgrid.Text = Left(flexgrid.Text, Len(flexgrid.Text) - 1)
                Else
                    KeyAscii = 0
                End If
            Case 48 To 57
                If Len(flexgrid.Text) < 5 Then
                    flexgrid.Text = flexgrid.Text + Chr(KeyAscii)
                Else
                    KeyAscii = 0
                End If
            Case Else
                MsgBox "Only Numbers allowed"
        End Select
    End Sub

    Private Sub flexgrid_LeaveCell()
        With flexgrid
            If Val(.TextMatrix(.Row, 2)) > Val(.TextMatrix(.Row, 3)) Then
                MsgBox "Col 2 is > Col 3"
            ElseIf Val(.TextMatrix(.Row, 2)) < Val(.TextMatrix(.Row, 3)) Then
                MsgBox "Col 3 is > Col 2"
            Else
                MsgBox "Col 3 = Col 2"
            End If
        End With
    End Sub
    0
     

    Author Comment

    by:swami_varahagir
    Hi Pepi,

    This LeaveCell event does not work. I have tried it before , also submitted the method before. Plz find some other alternative.

    Thanks for the suggetion.

    Thanks,

    0
     
    LVL 6

    Expert Comment

    by:PePi
    I've tested the code I've provided above and it works just fine. I think the problem you are having is that your code in the KeyPress event is a bit off. Try and change your KeyPress event code with the one I provided above. Now, using the up, down, left or right arrow will trigger the leavecell event.

    let me know how it turned out.


    0
     

    Author Comment

    by:swami_varahagir
    Hi Pepi,

    I modified the code as u have instructed bit the problem is every time I hit the key it fires the LeaveCell event.

    If say cell2 contains 45 and I move to cell3 and try to enter something, it fires the leavecell event and then puts the va;ue into the cell.

    I want it to check when I complete the data entry and navigate to another cell or change my focus to another control in the form.

    Thanks,
    0
     
    LVL 6

    Accepted Solution

    by:
    even if you use leavecell or rowcolchange event, when set the focus on cell2 and move to cell3, those event will fire.

    i have tested the code i gave you and when i type or enter numberic values, leavecell doesn't fire. did you change your flexgrid's KeyPress event codes to the above code?
    0
     

    Author Comment

    by:swami_varahagir
    Yes I did....

    If you could zip it and send to my email it will; be appreciated.

    swami_v3@yahoo.com.

    Thanks
    0
     
    LVL 1

    Expert Comment

    by:atheekurrahman
    compare the values for every event that cause the grid to lose focus.
    i.e for
            form_click() , msflexgrid_click , and for all other controls in the form(buttons and stuff)

    if u r using a text box to edit the grid then use this as well

    Private Sub txtValueEditor_KeyDown(KeyCode As Integer, Shift As Integer)
       With flexgrid
          Select Case KeyCode
             Case 13   'ENTER
                .SetFocus
             Case 27   'ESC
                txtValueEditor.Visible = False
                .SetFocus
             Case 38   'Up arrow
                .SetFocus
                DoEvents
                .Row = IIf(.Row > .FixedRows, .Row - 1, .Row)
             Case 40   'Down arrow
                .SetFocus
                DoEvents
                .Row = .Row + 1
          End Select
       End With
    End Sub
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now