verify format in userform textbox - how to?

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I have a textbox in a userform and have an entry format issue.

I am requesting the user enter a payment amount and need to be certain it is digits only containing zero, one, or two decimal places.
It can not contain characters other than digits or period for decimal place.

Examples of acceptable entries:
200
2100
150.26
150.2

Examples of unacceptable entries:
2,100
150.253
$50

How to I write an error handler for this?

Thanks, TTom
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Tom

Why don't you convert the output using the CDbl text converter, then format the output into your specified form

something like

format(cdbl(textbox1.text),"#.##")

That way, the entry can contain the different types, but the output will always be in a consistent format.

Tony
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
Using:
textbox1=format(cdbl(textbox1.text),"#.##")
works great! :cool:

Thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
It can not contain characters other than digits or period for decimal place.
Tom,
textbox1=format(cdbl(textbox1.text),"#.##")
should work fine if your users always cooperate & enter nothing but numeric characters.
However if any alpha characters get entered it'll generate an error.
You could use something like this to allow only numeric characters and/or a decimal point
from the keyboard. All other entry keys are disabled while the textbox has focus.
Code:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 46 Or KeyAscii > 57 Or KeyAscii = 47 Then KeyAscii = 0
End Sub
 

Forum statistics

Threads
1,136,502
Messages
5,676,225
Members
419,614
Latest member
bfletcher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top