Change decimal place in MSFlexgrid based on combo box selection...

I am using an MSFlexgrid with 2 columns, textbox for editing purposes, and a combo box.  In column 1 the user will enter a six digit value and it will automatically format to 3 decimal places (i.e. 123456 will be 123.456) In column 2, the user enters another six digit value and the decimal location in this column depends on the user's selection from a combo box (i.e their options are ddddd.d, dddd.dd, ddd.ddd, dd.dddd, d.ddddd).  My question is, is there any way to update the values in Column 2 if the user has already entered their values in the flexgrid and decides to change their decimal place selection in the combo box.  I would think I should be able to run a loop through column 2 using the combobox1_change event, but no luck so far.  Just to clarify, if the user types the value 123.456 into textmatrix(0,2) and they change their selection to dddd.dd then I would like the flexgrid to automatically show 1234.56 to reflect the change.  Any suggestions would be greatly appreciated...
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.

taskyCommented:
Sloppy, but works
Dim strTmp As String
strTmp = MSFlexGrid1.Textmatrix(0,2)
strTmp = Replace(strTmp, ".", "")
MSFlexGrid1.TextMatrix(0,2) = Format(Val(strTmp), "0.00000") ' For Example would do like.. 1.29300

Open in new window

0
taskyCommented:
Also, you should be using the Combo1_Click event, Change only fires when text is entered into the combobox. And although the name is Click, the event is fired whenever the selected index changes.
0
AJKBOCCommented:
Anjinsan5,

add in the Click Event of your Combo Box the following code:

-----------------------------------------------------------------------------------------
dim i as integer

For i = 2 to MSFlexGrid1.Rows-1
       Select Case ComboBox1.text
              Case "ddd.ddd"
                      MSFlexGrid1.TextMatrix(i,2)=format (MSFlexGrid1.TextMatrix(i,1),"000,000") 'Use might need to use "000.000" depending on your regional settings
               Case ..... 'You list the rest of the cases here
               .
               .
       End Select
Next i

--------------------------------
I hope this will help you. Let me know if u need any clarifications.

Thanks
Savvas
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!

taskyCommented:
AJK's solution is way too much code. You can modify it as follows:


Dim I as Integer
Dim sFormat As String 
sFormat = Replace(ComboBox1.Text, "d", "0") ' Prepare the format string
' For example, dd.dddd is turned into 00.0000
For i = 2 to MSFlexGrid1.Rows-1
 MSFlexGrid1.TextMatrix(i,2)=format(MSFlexGrid1.TextMatrix(i,1),sFormat)
Next i

Open in new window

0
Anjinsan5Author Commented:
Thank you tasky & AJKBOK for your suggestions...both have steered me in the right direction.  However, the default format for column 2 is "ddd.ddd", so when the user types 123.456 in the column
and goes to change the decimal type (combo box selection) the following happens:
For
"dddddd" - 000123 appears instead of 123456
"ddddd.d" - 00123.5 appears instead of 12345.6
"dddd.dd" - 0123.46 appears instead of 1234.56
"dd.dddd" -  123.4560 appears instead of 12.3456
"d.ddddd" - 123.45600 appears instead of 1.23456
Any ideas?
0
AJKBOCCommented:
Anjinsan5,

change line

          MSFlexGrid1.TextMatrix(i,2)=format(MSFlexGrid1.TextMatrix(i,1),sFormat)

to this line

         MSFlexGrid1.TextMatrix(i,2)=format(replace(MSFlexGrid1.TextMatrix(i,1),".",","),sFormat)

and u should be fine.

Savvas
0
Anjinsan5Author Commented:
AJKBOC,

Tried your latest suggestion, and made a case for each condition, however now simply changes the default 123.456 to 123456 and only fires once...
0
Anjinsan5Author Commented:
Any suggestions?
0
AJKBOCCommented:
Anjinsan5,
 
Try this code in the ComboBox1_Click Event. This is another solution. I am sure it will do the trick.

Regards,
Savvas
Dim dotPos As Integer
Dim strNum As String
Dim i As Integer
 
dotPos = InStr(1, Me.Combo1.Text, ".")
 
For i = 2 To MSFlexGrid1.Rows - 1
    'Get the number without any ., if there are any
    strNum = Replace(MSFlexGrid1.TextMatrix(i, 1), ".", "")
    
    'If it;s the dddddd case the set the number as it is stored in strNum.
    If dotPos = 0 Then
         MSFlexGrid1.TextMatrix(i, 2) = strNum
     Else
         'If there is a . in the combo box format, then get the dotpos-1 left digits of the
         'number, add the . and add the rest digits after the dotpos.
         MSFlexGrid1.TextMatrix(i, 2) = Left(strNum, dotPos - 1) & "." & Mid(strNum, dotPos)
    End If
Next i

Open in new window

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
AJKBOCCommented:
In my code above, instead of Combo1 in line 5 write ComboBox1.

Savvas
0
Anjinsan5Author Commented:
Great help!
0
Anjinsan5Author Commented:
Works great! Modified it to fit my needs.  Thanks so much for your help!  
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.