Prevent commas & pounds being entered into textbox

[Katherine]

I have a textbox in my userform for a price to be entered. Because of the way
we intend to use the information, the price needs to be purely numeric - no
pound signs and no commas or full stops.

Somebody on the boards helped me out with the following piece of code, which
throws up a messagebox if the user tries to put text in the textbox, but I
also need to somehow prevent them from entering commas or pound signs.

Because I don't really understand exactly what this code is doing (what's
the CLng bit all about?), I have no idea how to tweak it into doing what I
want. Can anyone provide any suggestions?


Private Sub txtPrice3_exit(ByVal cancel As MSForms.ReturnBoolean)
Dim x As Long
On Error GoTo Invalid
If txtPrice3.Text = "" Then Exit Sub
x = CLng(txtPrice3.Text)
Exit Sub
Invalid:
MsgBox "Price must be a number." & vbCrLf & vbCrLf & _
"Please don't use any pound signs (£) or other punctuation.", _
vbExclamation, "Invalid Price!"
txtPrice3 = Null
cancel = True
End Sub

 

[Doug Robbins]

Getting rid of the currency symbol, the thousands separator and the decimal
point is easy. What is going to be hard is to ensure consistency of
inclusion of the cents or pennies or whatever they are. If someone enters
100 how do you know if its 1 (major unit of currency) or 100 of them. You
may get more consistency if you require users to enter the "dollars and
cents" and then use the following (at the appropriate time, and probably not
directly to the text box into which the user enters the amount.)

Format(TextBox1.Text * 100, "#")

Better to leave the user seeing what they entered and do the conversion when
you make use of the entry

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Katherine]

Fortunatly Doug, the price is going to be the price of a property (the
userform is for an estate agent), so it's safe to say none of the values will
have pennies in them - it'll be more in the range of £100,000 / £250,000 etc

 

[Doug Robbins]

In that case, omit the *100 from the code. Just use

Format(TextBox1, "#")

it will strip out any currency symbol or thousands separator that the use
inserts.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Katherine]

Hi Doug,

I've tried this out this morning and it doesn't work. I've put the code in
the submit_click procedure but it's coming up with a compile error saying
that it expected an =

If I change it to Format (txtPrice2.Text = "#") then the vba editor will
accept it, but it still doesn't actually work - the value from the textbox is
inserted into the document complete with £ and commas.

What am I doing wrong?

 

[Doug Robbins]

Right, it appears that it will not handle the deletion of the £. If you
were to include a £ in a label, immediately to the left of the text box,
that should discourage people from entering one into the text box. The
format function does delete at $ sign and I was assuming it would do the
same for the £, but as I am working on a tablet PC that does not have a
separate numeric keypad, I did not try it.
--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP