[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). :)

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.

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.

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.

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. :)