Limiting decimal place

[Takes2ToTango]

Hi All,

Is there a way to limit decimal places? For example, I want to be able to write as many numbers before the '.' but as soon as the decimal is inserted, I want to limit it to 4 numbers afterward. So for example - 33.1234. Once 4 is inserted, then the user can no longer input any numbers.

 

[RobP]

Hi,

I don't believe you can stop a user typing infinite digits into any cell, but you could use a popup box or the like to ask them to enter it, and then act on that to store it in some place.

Also, a good tool could be this as a formula :

=TRUNC(number,[num_digits])

As you can specify how many digits you want your formula to use from that cell (eg. it might have .2345678 after your point, but if you use [4] in your number of digits, it will only use those..

Any use ?
Rob

 

[Takes2ToTango]

Hi Rob,

Sorry, i forgot to add that this is for a userform textbox! Can you use that same code for this Vba textbox?

 

[RobP]

Hi,

well thats a little different, but perhaps better for you. There is no Trunc() in VBA sadly.

So my thought would be to use something like INSTR() to search for the "." , then use the left hand part of the string up to the dot, and add the right hand 4 chars after the "." to it by using concatenate() or a simple "&" ?

Rob

 

[Joe4]

Maybe take a look here: VBA userform to worksheet - 2 decimal places only

 

[RobP]

as an example :

Sub test()

Dim textval As String

textval = InputBox("data")
pt_pos = InStr(1, textval, ".")

If pt_pos > 0 Then

    result = Int(textval) & Mid(textval, pt_pos, Len(textval) - pt_pos)

End If



End Sub

 

[Takes2ToTango]

Hi All,

Many thanks for you replies. Joe, that link you provided had the answer!
Rob, many thanks - I used your code with the text input but the input didn't seem to populate the main text box

 

[RobP]

No problem, as long as you found your answer.

My code just stored the answer in "Result" after you typed it in - so then its up to you what to do with it / where to put it ..

Thanks for feedback

Rob

 

[Takes2ToTango]

No problem, as long as you found your answer.

My code just stored the answer in "Result" after you typed it in - so then its up to you what to do with it / where to put it ..

Thanks for feedback

Rob

That could be why I couldn't see it. I don't think I told it where to 'add' it too

 

[Joe4]

You are welcome.
Glad we were able to help!