[RESOLVED] Missing decimal places in Userform textbox (Word 2003)
Within my userform there are some text boxes that require a price input. However, I want to set it up so that if a user enters, for example, "950", it will be converted to "950.00" (including decimal places).
Here's what I have:
VB Code:
Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'SELLING PRICE ENTRY FORMAT VALIDATION
If IsNumeric(txtPrice.Value) Then
txtPrice.Value = Format(CLng(txtPrice.Value), "#,###.##")
Else
MsgBox "Please enter a valid selling price!", vbExclamation + vbOKOnly, "Invalid Price Format!"
txtPrice.Value = Null
Cancel = True
End If
End Sub
Using this code, if I enter "950", I get "950." (it shows the decimal point, but nothing else after that). I also lose the the trailing zeros even if I enter them. For example, I enter "950.00", but again get "950".
I know there is a currency format that can be used, but I had problems with that as well and believe that the currency format relies on regional settings, which I don't want to do.
I also considered FormatNumber, but my userform may be used in Word 97, which apparently doesn't support this function?
Any help would be appreciated (and probably relieve my headache). :)
Re: Missing decimal places in Userform textbox (Word 2003)
VB Code:
dim spaces as integer
spaces=Instr(txt,1,".")
spaces=len(txt)-spaces
If(spaces=1) then
txt=txt & "0"
else if (spaces=2) then
txt=txt & "00"
else
txt = txt & ".00"
end if
This may not work exactly I didnt test the code but it shows the gist of it.
Re: Missing decimal places in Userform textbox (Word 2003)
I'm afraid I don't quite understand this reply or how to apply it. :confused:
Reading the code it looks as if it simply adds ".00" to the value entered in the text box (although this is a guess - my username says it all).
If I take my original code and change the format to "#,###.00" instead of "#,###.##" then I will get my 2 trailing zeros. However, if a user enters, for example, "949.95" it gets rounded up to "950.00" and I don't want the number to be rounded beyond 2 decimal places.
Re: Missing decimal places in Userform textbox (Word 2003)
Your rounding issue is because your converting to a LONG variable using CLng(), which must be a whole number. For a decimal conversion use CDec(). Essentially the called function is just detecting where the decimal is, and if there is already something after a character detect how much has to be filled in.
VB Code:
Public Function FormatToNumber(txt as String) as String
dim spaces as integer
'Find the decimal position
spaces=Instr(txt,1,".")
'Determine if there is even a decimal
If(spaces>=0) Then
'Determine how many positions are left between the decimal
'and end of text
spaces=len(txt)-spaces
If(Spaces=1) Then
'Value only has one number after the decimal
txt=txt & "0"
End If
If(spaces=2) Then
'Value has no numbers after the decimal
txt=txt & "0"
End If
Else
'Value has no decimals
txt=txt & ".00"
End If
FormatToNumber=txt
End Function
It is possible to add a function to add the "commas" as well to seperate thousands millions etc.. but I will leave that to you.
Warning: Double check Instr() parameters, don't think I put the ones in the correct order.
Re: Missing decimal places in Userform textbox (Word 2003)
Although I really appreciate the help, I'm afraid that this solution is a little out of my depth (for the moment).
In the meantime, I managed to fix it with this:
VB Code:
Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'SELLING PRICE ENTRY FORMAT VALIDATION
If IsNumeric(txtPrice.Value) Then
txtPrice.Value = Format$(CDec(txtPrice.Value), ".00")
Else
MsgBox "Please enter a valid price!", vbExclamation + vbOKOnly, "Invalid Price Format!"
txtPrice.Value = Null
Cancel = True
End If
End Sub
I have checked it with integers and decimals and it gives the correct result everytime. The only thing that is a little strange is that I have set the text box to a maximum of 10 characters - now when I enter a 10 digit number it adds .00 to the end making a total of 13. This, however, should not cause any problems because the price should not exceed 9999.99 so I have plenty of room.
Again, thanks for trying - it's much appreciated. And maybe one day I will get to the point where I can understand how to apply your solution. :)