Solved: Limit to 4 decimals in textbox using VBA

[nagavineela]

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.

Thanks in advance,
Vineela

[lynnnow]

From where is the data getting populated and how have you called the value? Please give some more details.

[nagavineela]

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

[Bob Phillips]

[vba]

txttotfailurerate.Value = Format(totfailrate, "#,##0.0000")
[/vba]

[nagavineela]

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

[Bob Phillips]

maybe like this

[vba]

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

[mikerickson]

[VBA]txttotfailurerate.Value = Format(totfailrate, "0.0000E+00")[/VBA]

[nagavineela]

Thank you. Its working.

[Bob Phillips]

Nice touch Mike, but that rounds too.

[nagavineela]

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.


Please help me to mark this thread as Solved.

Thanks,
Vineela

[Bob Phillips]

It is an option in Thread tools at the head of the threads.

[nagavineela]

Thank you XLD. Marked it as solved.

[mikerickson]

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

[VBA]With txttotfailurerate
.Value = Format(totfailrate, "0.0000E+00")
.Tag = CStr(totfailrate)
End With[/VBA]
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]

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.