need help with textbox validation problem 
Author Message
 need help with textbox validation problem

Hi, I'm having some problems validating a textbox field on a userform
within EXCEL97. In addition to my textbox I also have an exit button
and  date and time picker control. My problems are these.

1) I have the following validation code on the exit event of my text
box. The idea being that you should not be able to leave this box
unless there is a valid number entered or its blank.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Cancel = False
If TextBox1.Value = "" Then
    Exit Sub
Else

' validation code goes here which
' sets itsvalid to false if its invalid
' and then ...

    If itsvalid = false Then
        MsgBox ("Invalid number, please re-enter")
        Cancel = True
        UserForm2.TextBox1.SetFocus
    End If
End Sub

This works fine except in the following scenario:- I'm in the textbox,
enter an invalid number then click on the down arrow of my date picker
control. I get the warning message about the number being invalid but
the focus does not seem to be getting set back to the textbox field as
the calendar drop down event of my date picker still occurs. Anyone
come across this problem before? How do I prevent this happening.

2) My other problem is this. If I'm on the texbox field enter an
invalid number then click on my exit button to quit out the form the
validation code still fires even though I've set the getfocusonclick
to false on my exit button. How do I set it up so that the texbox
validation code does NOT fire when I click my exit button?

Suspect I may need to use API/hooks to solve this problem but I not
experienced in using these techniques. Any help is much appreciated



Tue, 23 Aug 2005 01:17:43 GMT  
 need help with textbox validation problem
1.  I'm not all that familiar wiht VBA, but your first problem sounds very
similar to a common problem in VB, which is that the code in an event runs
BEFORE the event actually occurs.  Thus, when you click on the date control,
your exit code fires, but the focus is actually moved to the date control
AFTER your exit code is completed.  There are lots of work arounds for this.
You might try setting a variable with formwide scope in your exit routine so
that when the GotFocus events of the other controls fire, the variable is
checked and if true, focus is set back to the textbox.

2.  I don't have an answer to this except to say that most programmers call
their validation code from the Save or Exit button, so you might solve both
problems by delaying error checking until the button is clicked.  Then it is
a simple matter to refocus on the textbox after alerting the user.


Quote:
> Hi, I'm having some problems validating a textbox field on a userform
> within EXCEL97. In addition to my textbox I also have an exit button
> and  date and time picker control. My problems are these.

> 1) I have the following validation code on the exit event of my text
> box. The idea being that you should not be able to leave this box
> unless there is a valid number entered or its blank.

> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

> Cancel = False
> If TextBox1.Value = "" Then
>     Exit Sub
> Else

> ' validation code goes here which
> ' sets itsvalid to false if its invalid
> ' and then ...

>     If itsvalid = false Then
>         MsgBox ("Invalid number, please re-enter")
>         Cancel = True
>         UserForm2.TextBox1.SetFocus
>     End If
> End Sub

> This works fine except in the following scenario:- I'm in the textbox,
> enter an invalid number then click on the down arrow of my date picker
> control. I get the warning message about the number being invalid but
> the focus does not seem to be getting set back to the textbox field as
> the calendar drop down event of my date picker still occurs. Anyone
> come across this problem before? How do I prevent this happening.

> 2) My other problem is this. If I'm on the texbox field enter an
> invalid number then click on my exit button to quit out the form the
> validation code still fires even though I've set the getfocusonclick
> to false on my exit button. How do I set it up so that the texbox
> validation code does NOT fire when I click my exit button?

> Suspect I may need to use API/hooks to solve this problem but I not
> experienced in using these techniques. Any help is much appreciated



Tue, 23 Aug 2005 03:57:36 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help needed on validation problems with text

2. Help with TextBox Lost Focus and Data Validation

3. Textbox problems...I need help.

4. Textbox problems...I need help.

5. Validation help needed, PLEASE

6. Date validation script needed - Please help!!!

7. Validation?????Newbie needs help

8. Need Help With Validation!!

9. Need help on dbcombo validation.

10. Need Help With Validation!!

11. Student Needing a Little Help : Re Validation

12. Date validation script needed - Please help!!!

 

 
Powered by phpBB® Forum Software