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.

Actually, the button was only there to close the form. I didn't put any code in
there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Norman Jones wrote:

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.

--

Dave Peterson

Hi Dave,

Actually, the button was only there to close the form. I didn't put any
code in there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Nevertheless, the fact that the button is inoperable (even without explicit
code) if an invalid entry is made in the textbox seemed to me a very
pleasing way of underlining your validation code.


---
Regards,
Norman


"Dave Peterson" wrote in message
...
Actually, the button was only there to close the form. I didn't put any
code in
there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Norman Jones wrote:

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

I think you added something to disable that button. (I was just using it as a
cancel button--and it was always enabled for me.)

But I do get your point. It's a nice way to make sure entries are valid before
continuing.

Norman Jones wrote:

Hi Dave,

Actually, the button was only there to close the form. I didn't put any
code in there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Nevertheless, the fact that the button is inoperable (even without explicit
code) if an invalid entry is made in the textbox seemed to me a very
pleasing way of underlining your validation code.

---
Regards,
Norman

"Dave Peterson" wrote in message
...
Actually, the button was only there to close the form. I didn't put any
code in
there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Norman Jones wrote:

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

--

Dave Peterson

Hi Dave,

It is a somewhat odd feeling to be involved in contention based on my liking
for your code!

In any event, and at the risk of boring all and sundry:

I think you added something to disable that button. (I was just using
it as a cancel button--and it was always enabled for me.)

Once Textbox1 is clicked, anything other than a valid entry prevents use of
the button via your code line:

Cancel = True 'don't leave the textbox

In other words, once the Textbox receives focus, for me at least, the
TextBox1_Exit code's Cancel instruction prevents *access* to the button.

As an aside, if this were not the case, what would be the significance of
the comment appended to the Cancel instruction?


---
Regards,
Norman


"Dave Peterson" wrote in message
...
I think you added something to disable that button. (I was just using it
as a
cancel button--and it was always enabled for me.)

But I do get your point. It's a nice way to make sure entries are valid
before
continuing.

Norman Jones wrote:

Hi Dave,

Actually, the button was only there to close the form. I didn't put
any
code in there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Nevertheless, the fact that the button is inoperable (even without
explicit
code) if an invalid entry is made in the textbox seemed to me a very
pleasing way of underlining your validation code.

---
Regards,
Norman

"Dave Peterson" wrote in message
...
Actually, the button was only there to close the form. I didn't put
any
code in
there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Norman Jones wrote:

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

--

Dave Peterson

Ahhh...

I get you.

I thought that you actually meant that the button was disabled--
me.commandbutton1.enabled = false

I read more into your response that you really meant.

Sorry for the (er, my) confusion.



Norman Jones wrote:

Hi Dave,

It is a somewhat odd feeling to be involved in contention based on my liking
for your code!

In any event, and at the risk of boring all and sundry:

I think you added something to disable that button. (I was just using
it as a cancel button--and it was always enabled for me.)

Once Textbox1 is clicked, anything other than a valid entry prevents use of
the button via your code line:

Cancel = True 'don't leave the textbox

In other words, once the Textbox receives focus, for me at least, the
TextBox1_Exit code's Cancel instruction prevents *access* to the button.

As an aside, if this were not the case, what would be the significance of
the comment appended to the Cancel instruction?

---
Regards,
Norman

"Dave Peterson" wrote in message
...
I think you added something to disable that button. (I was just using it
as a
cancel button--and it was always enabled for me.)

But I do get your point. It's a nice way to make sure entries are valid
before
continuing.

Norman Jones wrote:

Hi Dave,

Actually, the button was only there to close the form. I didn't put
any
code in there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Nevertheless, the fact that the button is inoperable (even without
explicit
code) if an invalid entry is made in the textbox seemed to me a very
pleasing way of underlining your validation code.

---
Regards,
Norman

"Dave Peterson" wrote in message
...
Actually, the button was only there to close the form. I didn't put
any
code in
there to enable/disable it.

(and maybe it's no longer so subtle, huh?)

Norman Jones wrote:

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

--

Dave Peterson

--

Dave Peterson