• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

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...
0
Anjinsan5
Asked:
Anjinsan5
  • 5
  • 4
  • 3
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now