Data validation using TextBox in UserForm

[Chris]

Using Excel 2000, I am building a UserForm with several textbox where
the user needs to input some data.
Basically, I want to prompt the user using MsgBox on invalid inputs and
put the focus back to the textbox.

I have found several solutions for validating the input, but none of
them meets my specific needs.

I have several pages (using multipage control) in the userform and each
page contains textboxes for data input.

1. txtbox_chage method
It works fine when the user inputs the wrong data. But it doesn't
prompt the user if the user doesn't change the input after the first
error message.
For example, if the user inputs the errorsome data, MsgBox pops up. But
after the msgbox, if the user decides not to change it and proceed to
the next input, it obviously doesn't work. (Since the invalid doesn't
change, the method is not executed anymore)

2. txtBox_BeforeUpdate method
This method seems to be popular. Just by setting Cancel parameter to
False, the focus never leaves the textbox.
However, it won't even let the user exit the userform without entering
a valid data into the errorsome entry.
(User can click on "Exit" command button which basically calls "Unload
Me")
Also, when the user clicks on the other pages (on the multipages) it
will display the msgbox twice.

3. txtBox_Exit method
Everything works as I wanted except when the user clicks on the other
pages.
I think when the user clicks on the other pages, the focus never
"exits" the textbox on that particular page, hence not calling the
method.
When the user clicks on the page containing errorsome entry again and
choose some other textbox, then it prompts the error message.


Is there any way to set the focus (i mean the cursor) to the textbox I
want?
SetFocus method doesn't seem to work properly either.

 

[Corey]

Have you tried using the :
1. textbox_change method
and adding a Textbox1.value = ""
at the end of the Code so the user MUST re-enter a value?

Corey....

 

[Chris]

Thanks for the input.
However, it doesn't fix the problem if the user just leave the textbox
empty and try to run the program.

If the users of the macro are sensible enough, they will enter
something to the textbox, but unfortulately, not all of them would be
sensible enough.

 

[Corey]

What about when the user runs the program(if by CommandButton) a line such
as :

If Textbox1.value = "" then
msgbox "There is NO Value in Textbox 1
Textbox1.select
else
' Run the program
end if



Corey....