Block user from entering Zero amount

A

ah

Hi;

I'm in the midst of developing a form by suing Microsoft Word. I would
appreciate if someone could help me to resolve the following issue urgently:

a) I've developed a claim form which allow the user to key in their claim
amount. I've inserted a "Text" field into the form. Is there a way for me to
block the user from entering zero amount? Please advise

Thanks for your help in advance.

Thanks
 
D

DaveLett

Hi ah,

I think you might be looking for something like the following:

Private Sub CommandButton1_Click()
If fValidClaimAmount Then
UserForm1.Hide
Unload UserForm1
End If
End Sub

Public Function fValidClaimAmount() As Boolean
fValidClaimAmount = False
If UserForm1.TextBox1.Text = "" Then
MsgBox "Zero (0) is not a valid claim amount.", vbCritical
UserForm1.TextBox1.SetFocus
Else
fValidClaimAmount = True
End If
End Function

I made the data check a function just in case there were other user form
controls that you wanted to use it with.

HTH,
Dave
 
G

Gordon Bentley-Mix

I'm assuming a few facts not in evidence here, so forgive me if I don't have
this quite right. For instance, I expect that you're developing a form by
"using" Microsoft Word rather than by "suing" it - although given the number
of complaints I've seen about Office 2007 lately, you might have more
success through the courts. ;-P

The other big assumption that I'm making is that by "form" you mean a VBA
solution that employs a UserForm, and that this UserForm contains a TextBox
control with the following validation requirements: the value entered must
be a number greater than 0 (zero). Although Dave is on the right track,
there is a more robust and immediate solution to meeting this requirement.

I would perform the validation in the Exit event for the TextBox rather than
at the end of the process. That way the user doesn't have to wait until
they've completed everything only to find out that they've made a mistake.
However, I would still use a function for the validation. This method has
advantages that mightn't be applicable to your situation but can come in
very handy at other times. Unfortunately, I think that's probably outside of
the scope of this discussion.

Here are the critical details of the use case that the following code
addresses: a TextBox control called "TextBox1" with the validation
requirements described above.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.Visible = True Then
Cancel = Not fcnValidateTextBox1
End If
End Sub

Private Function fcnValidateTextBox1() As Boolean
fcnValidateTextBox1 = True
Select Case True
Case Len(Trim(TextBox1.Value)) = 0 'Cannot be blank
GoTo TextBox1Fail
Case IsNumeric(TextBox1.Value) = False 'Must be a number
GoTo TextBox1Fail
Case CInt(TextBox1.Value) < 0 'Must be a positive number
GoTo TextBox1Fail
Case Else 'Any other value is OK
'Do nothing
End Select
Exit Function

TextBox1Fail:
MsgBox "The value entered for TextBox1 must be a number greater than 0
and cannot be blank.", vbCritical, "TextBox1 Error"
fcnValidateTextBox1 = False
End Function

A word of caution about the above. If this was for an actual project of
mine, I would leave out the check to force entry of a value (Case
Len(Trim(TextBox1.Value)) = 0). Instead, I'd probably put some kind of
indicator that the field is "required" (a red label or something) and place
the "cannot be blank" check into the final validation process; e.g. the one
tied to the click event of the 'OK' button or similar. People tend to get
really annoyed if they can't move past a field without entering something,
so they end up putting in a bogus value and then forget to update it later.

I'm happy to explain anything that might not be clear - like why I use the
structure "Len(Trim(...))" or why I set the function to True at the very
beginning. Just post back with any questions.

Of course, if my assumptions are all wrong and you're actually talking about
a form with FormFields on it (and a little voice in the back of my head
tells me that this is a distinct possibility and I've been led astray by
Dave's post), then none of this applies. Instead, you'll need to use an
OnExit macro associated with the FormField. Unfortunately, I almost *never*
work with forms and FormFields so I don't have the knowledge of what such a
macro would look like immediately to hand. However, there are certainly
others here who would be able to help, and at the very least I might have
helped Dave make his code better as well. :-D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top