Limit edit boxes to number format

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56
is it possible to limit edit boxes to only numbers? like the number format
2?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi everscern,

I assume you're really referring to textboxes, not edit boxes. Edit boxes are only used on Dialog sheets, which became obsolete with Excel 97. To limit a textbox to numeric entry, simply place code like the following into the code module of the sheet or userform on which the textbox resides:

Private Sub TextBox1_Change()
If TextBox1.Text = "" Then Exit Sub
If IsNumeric(TextBox1) Then Exit Sub
TextBox1 = ""
End Sub

This will simply clear the textbox if non-numeric text are entered. This will work for everything except exponential notation (e.g., 1.602E-23) because the 1.602E will not be recognized as numeric by the IsNumeric function. But such numbers can be entered if one is willing to enter 1.60223 first, then move the cursor back and enter the E-.

Similiar code will also work for edit boxes (in case you really did mean edit boxes). Let me know if this is the case and I'll provide it.
 

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56
bbb-2.jpg


I din know it was obsolete until you mention it. But i'm still using edit boxes. Notice the pic above. Thanks
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again everscern,

Since you actually are using a Forms editbox, you can limit the input to either "numbers" or "integers" (which is what I think you want in this case) by simply double-clicking on the editbox's border. This should cause the Format Control dialog to pop up. On the Control tab, select the Integer option buttion in the Edit Validation group box.

I do recommend that you switch to using userforms instead of the obsolete but still supported dialogs as you build new forms. They are both easier to use and provide more capability.

Keep Excelling.

Damon
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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