I have an Excel form and before it passes the data back to the worksheet, I
want to confirm that the user has entered a number into the field and that
the number is within a certain range...any ideas on how to do that?

Even better would to confirm the data when they leave the field.

Thanks.

Hello sneagle,

When you say Excel Form, do you mean a modified Worksheet for data
entry (tab from cell to cell) or a VBA User Form (looks like a Window)?
What type of control is the user typing the number into - TextBox or
ComboBox?

Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=482691

Sorry.

VBA UserForm
Textbox

"Leith Ross" wrote:


Hello sneagle,

When you say Excel Form, do you mean a modified Worksheet for data
entry (tab from cell to cell) or a VBA User Form (looks like a Window)?
What type of control is the user typing the number into - TextBox or
ComboBox?

Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=482691

One way:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim ValueIsOk As Boolean
ValueIsOk = False
If IsNumeric(Me.TextBox1.Value) Then
If Val(Me.TextBox1.Value) 3 _
And Val(Me.TextBox1.Value) < 6 Then
'it's ok
ValueIsOk = True
End If
End If

If ValueIsOk Then
Me.Label1.Caption = ""
Else
Cancel = True 'don't leave the textbox
Me.Label1.Caption = "Please enter a value between 3 and 6"
End If

End Sub



sneagle wrote:

Sorry.

VBA UserForm
Textbox

"Leith Ross" wrote:


Hello sneagle,

When you say Excel Form, do you mean a modified Worksheet for data
entry (tab from cell to cell) or a VBA User Form (looks like a Window)?
What type of control is the user typing the number into - TextBox or
ComboBox?

Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=482691



--

Dave Peterson

Thanks...way too tired now to try it, but why the first lines...option
explicit and Unload me?

Thanks again.

Hi Sneagle

Thanks...way too tired now to try it, but why the first lines...option
explicit and Unload me?

Adding the Option Explicit statement at the top of a code module ensures
that all variables have to be explicitly declared. This represents good
programming practice. For more information on the use of Option Explicit and
the declaration of variables, see Chip Peareson at:

http://www.cpearson.com/excel/variables.htm

Dave added the statement at the head of his code as a subtle way of
advocating its universal use.

By adding the command button code, Dave provides a convenient way of
demonstrating his suggested validation code: the button can only be used to
close the form if the validation conditions are satisfied.


---
Regards,
Norman



"sneagle" wrote in message
...
Thanks...way too tired now to try it, but why the first lines...option
explicit and Unload me?

Thanks again.