[RESOLVED] Missing decimal places in Userform textbox (Word 2003)

[New2vba]

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:

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

[BillBoeBaggins]

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.

[New2vba]

I'm afraid I don't quite understand this reply or how to apply it.

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.

[BillBoeBaggins]

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.

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.

[New2vba]

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:

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.