MSFlexgrid column does not hold value when moving into new cell.

I am using an MSFlexgrid with 2 columns and a textbox placed over it for editing purposes.  I have one column which automatically formats a user entered 6 digit numeric value to 3 decimal places (i.e. 123456 to 123.456), and a second column which takes a 6 digit user entered value and places the decimal based on a combo box selection made by the user.  I call the function from the Text_Change event, and whenever I enter a value in Col1 and navigate to a new cell, the entered value disappears.  I've attached some code below for how Col 1 is formatted.  Any ideas?  
Private Function Col1todecimal(str As String) As String
    Const dec = "."
Select Case Len(str)
    Case 1
        Col1todecimal = str & "00" & dec & "000"
    Case 2
        Col1todecimal = str & "0" & dec & "000"
    Case 3
        Col1todecimal = str & "" & dec & "000"
    Case 4
        Col1todecimal = Left(str, 3) & dec & Right(str, 1) & "00"
    Case 5
        Col1todecimal = Left(str, 3) & dec & Right(str, 2) & "0"
    Case 6
        Col1todecimal = Left(str, 3) & dec & Right(str, 3)
End Select
End Function
 
Private Sub Text1_Change()
Dim lRow As Integer
Dim sFormat As String
Text1.MaxLength = 6
MSFlexGrid1.Text = Text1.Text
With MSFlexGrid1
.Redraw = False
For lRow = MSFlexGrid1.FixedRows To MSFlexGrid1.Rows - 1
MSFlexGrid1.TextMatrix(lRow, 1) = Col1todecimal(MSFlexGrid1.TextMatrix(lRow, 1))
.Redraw = True
Next lRow
End With
End Sub

Open in new window

Anjinsan5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
Hi again,

Try to replace th text1_change event with this:

Private Sub Text1_Change()
Dim sFormat As String
Dim lRow As Integer
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = todecimal(Text1.Text)
End With
End Sub
0
Anjinsan5Author Commented:
Hi,
I tried that and it does work for holding the value now however it also sets Column 2 to the same format as Column 1 (i.e. 123.456) and when navigating in and out of cells it distorts the value (i.e. 123.456 when navigated into a cell which holds that value it changes the value each time until it ends up looking like 123....).  At least I have something to work with though.  Thanks!  Please let me know if you have any other ideas...  
0
Jorge PaulinoIT Pro/DeveloperCommented:
I have made this example at work. I don't have here VB6 (as you may already know) but in my small example worked fine. I can upload it tomorrow but if you can show me your code (SFlexGrid1 and Text1 events) maybe I see something.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anjinsan5Author Commented:
Sure thing! attached those events below...
Private Sub Text1_Change()
Dim lRow As Integer
Dim sFormat As String
Text1.MaxLength = 6
MSFlexGrid1.Text = Text1.Text
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
End With
End Sub
 
Private Sub MSFlexGrid1_EnterCell()
    Text1.Left = MSFlexGrid1.Left + MSFlexGrid1.CellLeft
    Text1.Top = MSFlexGrid1.Top + MSFlexGrid1.CellTop
    Text1.Text = MSFlexGrid1.Text
    
End Sub
 
Private Sub MSFlexGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyDelete Then
        MSFlexGrid1.Text = ""
        End If
End Sub
 
Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
 
With Text1
     Select Case KeyAscii
        Case vbKeyReturn, vbKeyTab
     '  move to next cell.
            If MSFlexGrid1.Col + 1 <= MSFlexGrid1.Cols - 1 Then
                MSFlexGrid1.Col = MSFlexGrid1.Col + 1
      Else
                If MSFlexGrid1.Row + 1 <= MSFlexGrid1.Rows - 1 Then
                    MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                    MSFlexGrid1.Col = 1
                Else
                    MSFlexGrid1.Row = 0
                   MSFlexGrid1.Col = 1
                End If
          End If
            Case 8
                If Not Text1.Text = "" Then
                  Text1.Text = Left(Text1.Text, Len(Text1.Text) - 1)
                End If
            Case 9 ' Tab
                If MSFlexGrid1.Col + 1 = MSFlexGrid1.Cols Then
                MSFlexGrid1.Col = 0
                    If MSFlexGrid1.Row + 1 = MSFlexGrid1.Rows Then
                      MSFlexGrid1.Row = 0
                    Else
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                    End If
                Else
                    MSFlexGrid1.Col = MSFlexGrid1.Col + 1
             End If
        Case 48 To 57 'numbers only
            Text1.Text = Text1.Text & Chr$(KeyAscii)
     Case Else
        End Select
    End With
End Sub
 
' the function for conversion to correct format
Private Function Col1todecimal(str As String) As String
    Const dec = "."
Select Case Len(str)
    Case 1
        Col1todecimal = str & "00" & dec & "000"
    Case 2
        Col1todecimal = str & "0" & dec & "000"
    Case 3
        Col1todecimal = str & "" & dec & "000"
    Case 4
        Col1todecimal = Left(str, 3) & dec & Right(str, 1) & "00"
    Case 5
        Col1todecimal = Left(str, 3) & dec & Right(str, 2) & "0"
    Case 6
        Col1todecimal = Left(str, 3) & dec & Right(str, 3)
End Select
End Function

Open in new window

0
Jorge PaulinoIT Pro/DeveloperCommented:
Well for start I think I haven't posted this

Private Sub Text1_Change()
Dim lRow As Integer
Dim sFormat As String
Text1.MaxLength = 6
MSFlexGrid1.Text = Text1.Text
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
End With
End Sub

Try this instead:
Private Sub Text1_Change()
Dim lRow As Integer
Dim sFormat As String
Text1.MaxLength = 6
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
End With
End Sub
0
Anjinsan5Author Commented:
Hi,
I tried that as well however same result.  The cell value holds, however when you navigate out of and back into the cell (let's say you wanted to change the cell value) it completely changes the value by deleting the characters to the right of the decimal.  If I enter a cell with a value it automatically changes the value from say 123.456 to 123.45.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry I have no deleted everythin in my last post. Change it to this removing Text1.MaxLength = 6:

Private Sub Text1_Change()
Dim lRow As Integer
Dim sFormat As String
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
End With
End Sub

If you count this 123.456 are 7 chars because the dot counts. You don't need to have this because you already validate in the  MSFlexGrid1_KeyPress. I have removed that in my example and it's running fine:
0
Anjinsan5Author Commented:
Tried that, no luck...what happens now is if you type more than 6 characters the cell value is deleted and still the same issue with navigating as before.  That's strange that it works good for you...it's got to be something hidden away in code that I'm missing.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
My final shot. Change this:

Case 6
        Col1todecimal = Left(str, 3) & dec & Right(str, 3)

To this:

Case 6
        Col1todecimal = Left(str, 3) & dec & Right(str, 3)
Case Else
        Col1todecimal = str

If this doesn't work I will send you(upload) my demo tomorrow.
0
Anjinsan5Author Commented:
It worked! Now, any idea how I can limit the max characters entered to 6 again because if the user tries to type in more it allows them and throws the format off.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Maybe after keypressed do something like this (note: not tested):

Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

 ' Case have a dot
 If InStr(1, Text1.Text, ".", vbTextCompare) > 0 Then
     If len(Text1.Text) >= 6 then exit sub
 Else
     If len(Text1.Text) >= 7 then exit sub
 End If
...
' Add a dot
Case 46
        Text1.Text = Text1.Text & Chr$(KeyAscii)

End Sub
0
Jorge PaulinoIT Pro/DeveloperCommented:
Any news ?
0
Anjinsan5Author Commented:
No luck yet...please post your example if you can so that I may compare as well.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anjinsan5Author Commented:
Great help! Worked with me every step of the way to find a solution.
0
Anjinsan5Author Commented:
Works great!  I just had to make a few changes to my code based on yours.  Is it still possible to format column 2 now, by changing the statement below?

With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
End With
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> Is it still possible to format column 2 now, by changing the statement below?

This formats the select row/column not all. So it's possible.

Thanks for the grade.
0
Anjinsan5Author Commented:
Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.