> Neil,
>
> If I may offer another tip: You might want to make your validation code
> "modular" - something like this. (I'll explain the advantages in a minute.)
>
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> If Me.Visible = True Then
> Cancel = Not fcnValidateTextBox1
> If Cancel = False Then FormatTextBox1
> End If
> End Sub
>
> Private Function fcnValidateTextBox1() As Boolean
> fcnValidateTextBox1 = True
> If Len(TextBox1.Value) > 0 Then
> If IsNumeric(TextBox1.Value) = False Then
> MsgBox "The value in TextBox1 must be numeric.", vbCritical,
> "TextBox1 Error"
> fcnValidateTextBox1 = False
> Exit Function
> End If
> End If
> End Function
>
> Private Sub FormatTextBox1()
> If Len(TextBox1.Value) > 0 Then TextBox1.Value = Format(TextBox1.Value, "
> $#,##0.00")
> End Sub
>
> In this approach I use a Boolean function to validate the value in the
> TextBox and use the result of this function to set the Cancel argument of the
> Exit event for the TextBox. (In my example above I want to make sure that the
> value is a number so I can format it as dollars, but it could be any sort of
> check - a date is the other common one I use.) The advantage of this approach
> is that you can then call the validation function in other places, such as
> the Exit event of the Frame control containing several TextBoxes or in the
> Click event of a CommandButton.
>
> For example, if you have a Frame control called Frame1 that contains three
> TextBoxes that must all be validated and all formatted, you could use
> something like this:
>
> Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> If Me.Visible = True Then
> If fcnValidateTextBox1 = False Then
> Cancel = True
> TextBox1.SetFocus
> Exit Sub
> Else: FormatTextBox1
> End If
> If fcnValidateTextBox2 = False Then
> Cancel = True
> TextBox2.SetFocus
> Exit Sub
> Else: FormatTextBox2
> End If
> If fcnValidateTextBox3 = False Then
> Cancel = True
> TextBox3.SetFocus
> Exit Sub
> Else: FormatTextBox3
> End If
> End If
> End Sub
>
> This is useful because (as you may have discovered) when you exit a Frame
> control, the Exit event for the Frame fires but the Exit event for the last
> control with focus does not. In the example above, I simply call the
> validation code for each TextBox in the Frame and set the Cancel argument of
> the Frame's Exit event to "True" if the validation on any TextBox fails (and
> call the formatting code for the TextBox if validation is successful). I also
> set focus back to the "failing" TextBox, which in most circumstances will be
> the last TextBox with focus anyway - think about if for a bit and I'm sure
> you'll see why.
>
> Finally, in the Click event of a CommandButton, I do something similar.
> Assume the same three TextBoxes with the same validation requirements as
> above but an additional requirement that none of the TextBoxes can be blank.
>
> Private Sub CommandButton1_Click()
> If fcnValidateTextBoxes = True Then
> 'Do the stuff that should be done when the button is clicked
> 'e.g. hide the UserForm, build the document, etc.
> End If
> End Sub
>
> Private Function fcnValidateTextBoxes() As Boolean
> If Len(TextBox1.Value) = 0 Then
> MsgBox "TextBox1 cannot be blank.", vbCritical, "TextBox1 Error"
> fcnValidateTextBoxes = False
> Exit Function
> ElseIf fcnValidateTextBox1 = False Then
> fcnValidateTextBoxes = False
> Exit Function
> Else: FormatTextBox1
> End If
> If Len(TextBox2.Value) = 0 Then
> MsgBox "TextBox2 cannot be blank.", vbCritical, "TextBox2 Error"
> fcnValidateTextBoxes = False
> Exit Function
> ElseIf fcnValidateTextBox2 = False Then
> fcnValidateTextBoxes = False
> Exit Function
> Else: FormatTextBox2
> End If
> If Len(TextBox3.Value) = 0 Then
> MsgBox "TextBox3 cannot be blank.", vbCritical, "TextBox3 Error"
> fcnValidateTextBoxes = False
> Exit Function
> ElseIf fcnValidateTextBox2 = False Then
> fcnValidateTextBoxes = False
> Exit Function
> Else: FormatTextBox3
> End If
> End Function
>
> I think you get the idea.
>
> BTW, if you use MultiPage controls in your UserForm, you can extend this
> approach to work with the Change event of a MultiPage - although you are
> somewhat limited in what you can do in this instance. (For example, you can't
> set focus to the control that fails validation because it's no longer visible,
> and there is no way to make it so.) It's a bit more complicated than the
> other examples I've posted so I won't go into it. However, if you do want to
> do something like this, you will need to evaluate the .Value property of the
> MultiPage control and check to see if the .Value is NOT the one you want to
> validate. And even then it's not perfect...
>
> Cheers!
>
> Gordon
>
> Neil Humphries wrote:
> >Your suggestion worked wonderfully. I much prefer avoiding the problem rather
> >than resolving it after the fact.
> >
> >Thanks
> >
> >> Neil,
> >>
> >[quoted text clipped - 17 lines]
> >> >programmatically close the msgbox before the userform Unloads? Or, how do I
> >> >keep the validation routine from triggering when the userform Unloads?
>
> --
> Message posted via OfficeKB.com
>
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200811/1>
>