How to format 2 MSFlexgrid columns to different decimal places, and enter values into textbox at runtime?

I am using Visual Basic 6 and have a form with an MSFlexgrid (2 columns) and a textbox which is used to edit the MSFlexgrid.  I have set the maxlength property of the textbox to 7 to limit the number of digits entered in.  I'm not sure what my options are but I would like to do the following:  Format the MSFlexgrid so that column 1 will automatically show the entered value to 3 decimal places and column 2 will place the decimal based on the user's choice from a dropdownlist box.  I have attached a snippet of my code to Format Column 1 only; what happens is It will place a decimal at then end of the digits entered instead of where it is supposed to.  
Private Sub FormatColumn1(sFormat as String)
With Text1
MSFlexGrid1.Redraw = False
For lRow = MSFlexGrid1.FixedRows To MSFlexGrid1.Rows - 1
sFormat = MSFlexGrid1.TextMatrix(lRow, 2)
sFormat = Format$(sFormat, "###.###")
MSFlexGrid(lRow, 2) = sFormat
Next lRow
MSFlexGrid1.Redraw = True
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,

You have to change this:
sFormat = Format$(sFormat, "###.###")

To:
sFormat = Format$(sFormat, "###.000")

But I don't understand the rest of the question. Can you try to explain better ?

jpaulino
0
Anjinsan5Author Commented:
Thanks for your response.  Unfortunately I tried that as well and what happens is the full value will appear with .000 at then end (i.e. 123456.000) instead of what I want which is 123.456.  As for the rest of the problem, hopefully this will explain better.  I have an MSFlexgrid form with 2 columns and 32 rows which is blank when the program is loaded.  I am using a textbox over the flexgrid to allow the user to enter values into the flexgrid while the program is loaded.  I need column one to automatically take the value the user types and set it to three decimal places ( so if they type 123456 the flexgrid will show 123.456).  Additionally, I need the second column to base it's decimal format off a combo box selection the user will make.  For example the user can choose between 00000.0, 0000.00, 000.000, and so forth.  Once they type their value into the second column, I want it to be displayed based on the option they choose.  Hope this helps!  
0
Anjinsan5Author Commented:
I think my issue may lie with the fact that I am using a textbox to edit the flexgrid or possibly that I may not be calling the FormatColumn1 procedure from the right procedure event.  
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!

Jorge PaulinoIT Pro/DeveloperCommented:
Sorry I post it wrong. Is sFormat = Format$(sFormat, "##,###.000")

As far as the combobox you already have it ? If so you just have to look the selection on the combobox (maybe using a select case method) and add it using the right format.
0
Jorge PaulinoIT Pro/DeveloperCommented:
You don't need to call the FormatColumn1. When you add the values you add with the right format.
0
Anjinsan5Author Commented:
Thanks again.  However now when I enter a value it shows up as 123,456.000 instead of 123.456.  Do you think it has to do with the fact that a textbox is doing the editing over the flexgrid? Also what event should I call my procedure from?  

As far as the combobox, yes I already have one in place.  I believe if I can get this format thing down for column 1 then I can hopefully figure out column 2 rather easily.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
That work fine for me. I think is got to do with the regional setting. Try this instead:

sFormat = Format$(sFormat, "##.###,000")
0
Anjinsan5Author Commented:
That's strange!  Now it gives me the value in this format:  123456.000000.  Basically, it's accepting that my textbox's maxlength property is set to 7 (with the 7th digit being a decimal pt) and it's adding zeros to the end of my value.  Where should I place the sFormat = Format$(sFormat, "##.###,000) in my code because at the moment I have the snippet as a private sub and I call it from the text1_change event.
0
Jorge PaulinoIT Pro/DeveloperCommented:
You don't have any button to accept the value ? You can use that or you can use the lost focus.

Can you show a picture of what you got! That part of the design.
0
Anjinsan5Author Commented:
No, there is no button to accept the value.  Rather, there is a button which takes all the user's selections from the rest of the form and does the necessary actions.  I have attached a picture...
flxgrdimg.bmp
0
Jorge PaulinoIT Pro/DeveloperCommented:
Strange! Can you upload the project (just that form)? I will take a look.
0
Jorge PaulinoIT Pro/DeveloperCommented:
You can use www.ee-stuff.com
0
Anjinsan5Author Commented:
I have attached all relevant code...I have alot of code on a single form that would be useless to you and probably confusing at best.  Hopefully this can help...
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 46, 48 To 57 'numbers only
            Text1.Text = Text1.Text & Chr$(KeyAscii)
        Case Else
        End Select
    End With
End Sub
 
Private Sub Text1_Change()
Dim sFormat As String
Dim lRow As Integer
MSFlexGrid1.Text = Text1.Text
Text1.MaxLength = 7
With MSFlexGrid1
.Redraw = False
For lRow = .FixedRows To .Rows - 1
sFormat = .TextMatrix(lRow, 2)
sFormat = Format$(sFormat, "###.###,000")
.TextMatrix(lRow, 2) = sFormat
Next lRow
MSFlexGrid1.Redraw = True
End With
End Sub

Open in new window

0
Jorge PaulinoIT Pro/DeveloperCommented:
I'm going to look now. Do you need this for the two columns right?
0
Anjinsan5Author Commented:
I do need to use the format for two columns however keep in mind that Column 1 will have the automatic format of ###.### (3 decimal places) and Column 2 will depend on the user's selection from a combo box (i.e. if they select 0000.00 then ####.## is the format).  Like you mentioned above, if we figure out a way to format column 1 then column 2 should be relatively easy.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
Change to this:

Private Sub Text1_Change()
Dim sFormat As String
Dim lRow As Integer
MSFlexGrid1.Text = Text1.Text
Text1.MaxLength = 7
With MSFlexGrid1
.Redraw = False
   For lRow = .FixedRows To .Rows - 1
      .TextMatrix(lRow, 2) = sFormat = Format(.TextMatrix(lRow, 2), "##,##0.000")
   Next lRow
MSFlexGrid1.Redraw = True
End With
End Sub

This is got to do with your system regional settings. You may need to change to "##.##0,000"
0
Anjinsan5Author Commented:
As if it couldn't get any worse...lol!  Now I'm getting true/false in column2 and it doesn't allow me to type numbers anymore.  
flxgrdimg-2.bmp
0
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry this is wrong!
.TextMatrix(lRow, 2) = sFormat = Format(.TextMatrix(lRow, 2), "##,##0.000")

change to this:
.TextMatrix(lRow, 2) =  Format(.TextMatrix(lRow, 2), "##,##0.000")

0
Anjinsan5Author Commented:
My regional settings are set to English (United States) and
Number: 123,456,789.00
Currency:  $123,456,789.00

Should these be changed?
0
Anjinsan5Author Commented:
No luck...I'm getting 123,456.000 when i type 123456.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
But when you type 123456 it will appear 123,456.000. This is correct.

And when you type 1234.56 it should appear 1,234.560
0
Anjinsan5Author Commented:
Yes, that is correct.  My goal was to have the user type a value without typing the decimal point and have it format automatically.  Is this possible?
0
Jorge PaulinoIT Pro/DeveloperCommented:
Everythings possible!!!

But when you decide if its decimal or not (according to this Number: 123,456,789.00)

1 = 1
12 = 12
123 = 123
1234 = ?
12345 = ?
123456 = ?
1234567 = ?
0
Anjinsan5Author Commented:
True...not sure why the numbers do not go to the right of the decimal unless you type a decimal in the cell though.  The format shouldn't care what the number is because it will turn it into a decimal value anyway.  At least I would think so.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
So how do you want ?
0
Anjinsan5Author Commented:
I want to have a blank field where the user types a 6 digit value and that value is automatically formatted to three decimal places.  I don't care if the decimal shows up in the flexgrid column on form_load, just as long as the value entered becomes formatted to three decimal places automatically.
0
Anjinsan5Author Commented:
If the user does not type in the full 6 digits then the final digits can be zeroes.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, how this should stay

1 = 1
12 = 12
123 = 123
1234 = ?
12345 = ?
123456 = ?
1234567 = ?

It's important to know because when the user is inserting it should be in the right format.
0
Anjinsan5Author Commented:
I'm not sure if I'm clear on your question but here goes...

1=100.000
12=120.000
123=123.000
1234=123.400
12345=123.450
123456=123.456
1234567=(not needed because maxlength is 7 and decimal counts as one character)

I hope this helps and I apologize in advance if my answer is unclear...
0
Jorge PaulinoIT Pro/DeveloperCommented:
I'm going to do. The big problem is that my system (Portugues) doesn't work that way.

I have to leave for some minutes. I will post back soon.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Hi again,

Since this will not be a real format you can add this function. When you need to use this value you must convert to the format you need using Cdec() to decimal or Cdbl() to double.

Public Function todecimal(str As String) As String
    Const sep = "."
   
    Select Case Len(str)
     Case 1
        todecimal = str & "00" & sep & "000"
     Case 2
        todecimal = str & "0" & sep & "000"
     Case 3
        todecimal = str & "" & sep & "000"
     Case 4
        todecimal = Left(str, 3) & sep & Right(str, 1) & "00"
     Case 5
       todecimal = Left(str, 3) & sep & Right(str, 2) & "0"
     Case 6
        todecimal = Left(str, 3) & sep & Right(str, 3)
     Case Else
        todecimal = ""
    End Select
   
End Function

Finally you just have to call it this way
 .TextMatrix(lRow, 2) =  todecimal(.TextMatrix(lRow, 2))
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:
I was away from my computer...my apologies!  The function above works great except for one major detail...it erases the value when I leave the cell.  Any ideas?
0
Jorge PaulinoIT Pro/DeveloperCommented:
You must show me how do you have using now. Show me your code again.
0
Anjinsan5Author Commented:
I used your function without changing it initially to see if it did what I needed and called it from the Text_1 Change Event and it worked except it would erase the value once you moved to the next cell.
See code below...  
Public Function todecimal(str As String) As String
    Const sep = "."
    
    Select Case Len(str)
     Case 1
        todecimal = str & "00" & sep & "000"
     Case 2
        todecimal = str & "0" & sep & "000"
     Case 3
        todecimal = str & "" & sep & "000"
     Case 4
        todecimal = Left(str, 3) & sep & Right(str, 1) & "00"
     Case 5
       todecimal = Left(str, 3) & sep & Right(str, 2) & "0"
     Case 6
        todecimal
 = Left(str, 3) & sep & Right(str, 3)
     Case Else
        todecimal = ""
    End Select
    
End Function
 
Private Sub Text1_Change()
Dim sFormat As String
Dim lRow As Integer
MSFlexGrid1.Text = Text1.Text
Text1.MaxLength = 7
With MSFlexGrid1
.Redraw = False
   For lRow = .FixedRows To .Rows - 1
    .TextMatrix(lRow, 2) = todecimal(.TextMatrix(lRow, 2))
   Next lRow
MSFlexGrid1.Redraw = True
End With
End Sub

Open in new window

0
Jorge PaulinoIT Pro/DeveloperCommented:
I don't have the VB6 in this machine. I can only test it latter but try to remove this from the function:

Case Else
        todecimal = ""
0
Anjinsan5Author Commented:
Tried that...still no luck!  I will play around with it for a while, hopefully something fixes itself.  
0
Jorge PaulinoIT Pro/DeveloperCommented:
You also don't have to do a loop in all the cells and change the format. You just need to change in the selected one.

I will check later and if you get any results just say it.
0
Anjinsan5Author Commented:
Thank you very much for your help!
0
Anjinsan5Author Commented:
Please let me know the results after you check...
0
Jorge PaulinoIT Pro/DeveloperCommented:
Anjinsan5,

Is it working ? I can only test tomorrow at work. If you still need help just say it and I will test.
0
Anjinsan5Author Commented:
Tried a few options yesterday, not many, however still stuck with the same issue.  I will try tomorrow again as well, and yes if you have any ideas I would appreciate it.  I will post letting you know if I figure it out.  Thanks!    
0
Jorge PaulinoIT Pro/DeveloperCommented:
Hi again and sorry for the delay!  I don't know if you still need it.

Try this:

Private Sub Text1_Change()
Dim sFormat As String
Dim lRow As Integer
With MSFlexGrid1
    .TextMatrix(.RowSel, .ColSel) = todecimal(.TextMatrix(Text1.Text))
End With
End Sub
0
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry

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:
Thanks again! Your solution fixed my problem with holding the value in the cell...because the textbox moves controls all editing it does however format column 2 with the same format as column 1 and when navigating in and out of cells the value becomes distorted(i.e. 123.456 when navigated into 3 times shows up as 123....).  At least you've given me something to work with now, I appreciate it.    
0
Anjinsan5Author Commented:
I had posted a new question regarding this matter earlier today, so if you have any more suggestions you can reference question ID: 23119261...seeing whereas you've answered most of the question already it would be a good way to earn more points if there is no better solution.  
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.