PDA

View Full Version : Solved: Limit to 4 decimals in textbox using VBA

nagavineela
09-15-2010, 04:56 AM
Hi,
I have created a textbox. The data in the textbox appears as 4.03775198082539E-04.
I need only 4 decimals to appear on the textbox.
Can anyone help me with VBA code to get the above result.

Vineela

lynnnow
09-15-2010, 05:06 AM
From where is the data getting populated and how have you called the value? Please give some more details.

nagavineela
09-15-2010, 05:40 AM
HI,
Thank you. I have data in excel and calculations were performed as below:
'Total Failure rate
Sheets("GroundFixed").Range("J" & i).Value = Sheets("GroundFixed").Range("H" & i).Value + Sheets("GroundFixed").Range("I" & i).Value
totfailrate = totfailrate + Sheets("GroundFixed").Range("J" & i).Value
txttotfailurerate.Value = totfailrate

Hence the above calculated value is populated in the text box "txttotfailurerate".

Please let me know if further clarification needed

xld
09-15-2010, 05:52 AM
txttotfailurerate.Value = Format(totfailrate, "#,##0.0000")

nagavineela
09-15-2010, 06:16 AM
Thank you. Result is rounding to 0.0003 but i need the result without rounding. For e.g. with the code i provided result is 3.62818373122415E-06
but as per the req it should display 3.6282E-06

xld
09-15-2010, 06:30 AM
maybe like this

Me.TextBox1.Text = Left\$(totfailrate, 6) & Right\$(totfailrate, Len(CStr(totfailrate)) - InStrRev(totfailrate, "E"))

mikerickson
09-15-2010, 06:36 AM
txttotfailurerate.Value = Format(totfailrate, "0.0000E+00")

nagavineela
09-15-2010, 06:42 AM
Thank you. Its working.

xld
09-15-2010, 06:46 AM
Nice touch Mike, but that rounds too.

nagavineela
09-15-2010, 06:46 AM
syntax 1 by XLD:
Me.TextBox1.Text = Left\$(totfailrate, 6) & Right\$(totfailrate, Len(CStr(totfailrate)) - InStrRev(totfailrate, "E"))

Syntax 2 by mikerickson:
txttotfailurerate.Value = Format(totfailrate, "0.0000E+00")

Both are working as expected. Thanks a lot to XLD and mikerickson.

Thanks,
Vineela

xld
09-15-2010, 06:47 AM

nagavineela
09-15-2010, 06:51 AM
Thank you XLD. Marked it as solved.

mikerickson
09-15-2010, 07:00 AM
Nice touch Mike, but that rounds too.

If one is looking to format a TextBox like a cell, displaying a rounded value while maintaining an accurate "underlying" value, he could do something like

With txttotfailurerate
.Value = Format(totfailrate, "0.0000E+00")
.Tag = CStr(totfailrate)
End With
and then access the "proper" value in the text box with Val(.Tag) rather than Val(.Value).

I don't see any way around using the .Tag (or similar) for the underlying value, if the .Text of the textbox is limited in how many decimals it should show. (vary the .Width to achieve a truncation?)

mikerickson
09-15-2010, 07:46 AM
Further thought:
The "actual" value could be maintained in the totfailrate variable, if it is a module wide variable, but..
If user is expected to enter a value (or accept the calculated value), in which case, display/actual value gymnastics are needed.

But, if the box is only to display the calculated value (in the desired format), using a Label, rather than a TextBox, would discourage the user from trying to change a value that they shouldn't.