restrict entry in a textbox to a range of values
 

I have a textbox that I want to restrict to a range of digits
(9200000000 - 9299999999).

I have tried various ways to bring up a message box when an incorrect
number has been entered, but everything I try seems to bring up the
error message AS you type, rather than when the complete number is
input.

I have managed to restrict to numerics and by field length sofar.

Can anyone help



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

restrict entry in a textbox to a range of values
 

Dillonstar,

Data | Validation

You can put the allowed values in the appriopriate dialogs.

Regards,
Kevin

"Dillonstar" wrote in message
...
I have a textbox that I want to restrict to a range of digits
(9200000000 - 9299999999).

I have tried various ways to bring up a message box when an incorrect
number has been entered, but everything I try seems to bring up the
error message AS you type, rather than when the complete number is
input.

I have managed to restrict to numerics and by field length sofar.

Can anyone help



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

restrict entry in a textbox to a range of values
 

Dillonstar,

I couldn't find the Data Validation for textboxes so...


Private Sub TextBox1_LostFocus()
Dim TBoxVal As Double
On Error GoTo ErrSec
If TextBox1.Value = "" Then Exit Sub
TBoxVal = CDbl(TextBox1.Value)
If TBoxVal < 9200000000# Or TBoxVal 9299999999# Then
TextBox1.Value = ""
MsgBox Prompt:="Your input was out of the range 9200000000" & _
vbCrLf & "& 9299999999. Please try again!"
TextBox1.Activate
Exit Sub
Else
Exit Sub
End If

ErrSec:
TextBox1.Value = ""
MsgBox Prompt:="Your input was non-numeric please try again!"
TextBox1.Activate
End Sub


Allows the textbox to remain blank, if you don't want this remove the
If TextBox1.Value = "" Then Exit Sub

Does not allow non-numeric entry, if you want to have this remove the
TextBox1.Value = ""
MsgBox Prompt:="Your input was non-numeric please try again!"
TextBox1.Activate

Dan E


"Dillonstar" wrote in message ...
I have a textbox that I want to restrict to a range of digits
(9200000000 - 9299999999).

I have tried various ways to bring up a message box when an incorrect
number has been entered, but everything I try seems to bring up the
error message AS you type, rather than when the complete number is
input.

I have managed to restrict to numerics and by field length sofar.

Can anyone help



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

restrict entry in a textbox to a range of values
 

Where is the textbox. Is it from the control toolbox toolbar or from the
drawing toolbar.

I assume it is on a userform and thus a control toolbox toolbar. You sound
like you are using the change event, but you probably want to use the Exit
Event.

You can use the keypress event to restrict entry to numbers and use the exit
event to check the number against your acceptable range.

Data|Validation is for cell entries if you want to move your input to the
worksheet.

--
Regards,
Tom Ogilvy

"Dillonstar" wrote in message
...
I have a textbox that I want to restrict to a range of digits
(9200000000 - 9299999999).

I have tried various ways to bring up a message box when an incorrect
number has been entered, but everything I try seems to bring up the
error message AS you type, rather than when the complete number is
input.

I have managed to restrict to numerics and by field length sofar.

Can anyone help



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

restrict entry in a textbox to a range of values
 

Dan,

You are right!

I need to slow down and read the question.

Regards,
Kevin


"Dan E" wrote in message
...
Dillonstar,

I couldn't find the Data Validation for textboxes so...


Private Sub TextBox1_LostFocus()
Dim TBoxVal As Double
On Error GoTo ErrSec
If TextBox1.Value = "" Then Exit Sub
TBoxVal = CDbl(TextBox1.Value)
If TBoxVal < 9200000000# Or TBoxVal 9299999999# Then
TextBox1.Value = ""
MsgBox Prompt:="Your input was out of the range 9200000000" & _
vbCrLf & "& 9299999999. Please try again!"
TextBox1.Activate
Exit Sub
Else
Exit Sub
End If

ErrSec:
TextBox1.Value = ""
MsgBox Prompt:="Your input was non-numeric please try again!"
TextBox1.Activate
End Sub


Allows the textbox to remain blank, if you don't want this remove the
If TextBox1.Value = "" Then Exit Sub

Does not allow non-numeric entry, if you want to have this remove the
TextBox1.Value = ""
MsgBox Prompt:="Your input was non-numeric please try again!"
TextBox1.Activate

Dan E


"Dillonstar" wrote in message
...
I have a textbox that I want to restrict to a range of digits
(9200000000 - 9299999999).

I have tried various ways to bring up a message box when an incorrect
number has been entered, but everything I try seems to bring up the
error message AS you type, rather than when the complete number is
input.

I have managed to restrict to numerics and by field length sofar.

Can anyone help



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

restrict entry in a textbox to a range of values
 

Thanks for your replys everyone, though I haven't quite got it working
yet.

I should have been more specific, but Tom, you assumed correctly that
this is a textbox as part of a form. I have successfully protected the
field to only allow numerics, and have restricted the maxlength of the
field. I have yet to restrict to the values I stated earlier.

I changed 'TextBox1' to my assigned field name, 'txtBAN', however when
I run the form the debugger does not like the line:

txtBAN.Activate

Any additional help would be most appreciated.

Dillonstar


--
Dillonstar
------------------------------------------------------------------------
Dillonstar's Profile: http://www.excelforum.com/member.php...fo&userid=1814
View this thread: http://www.excelforum.com/showthread...hreadid=159534