Text box .SetFocus Problem & Data Validation

[Maneesh Massey]

Hello Everyone,
I have been raking my brains out on this one for the past one week but haven't found anything useful the this topic. Maybe I was not looking in the right place.

I have been working with Text box validation techniques for the past week and need to validate user input from the userform in an Excel VBA application. I need to check to see for example, that no one enters numeric values into a text box which built for string entries. (You will admit, it is next to impossible to validate every entry the user can make into a form.) Like for example, how can you validate an email address ??

The question that I want to address is : why does the Text box's .SetFocus method not work and put the cursor into the textbox with the following code:

Private Sub Arec7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(Me.Arec7.Value) Then
    MsgBox "Please enter a valid name."
    Arec7.SetFocus
ElseIf Me.Arec7.Value = "" Then
    MsgBox "You must enter a name."
    Arec7.SetFocus
End If
End Sub

By the way, Arec7 is the name of the textbox given above.

I tried the Cancel=True method too but this gives odd behavior to the functionality of the form and the cursor becomes "Sticky", meaning to say, the cursor becomes stuck in the textbox and is unable come out. No other button works except the 'X' close button on the userform.

So, why does the .SetFocus method not work ??? And what is the best way to validate string values, numeric values and email addresses ??? I want to validate each textbox entry on KeyPress method while the user is still typing instead of before leaving the userform. It makes no sense to me to validate textbox entries when the user is out of the textbox and about to leave the form.

Would anyone like to help please ?? Greatly appreciate all help !
Regards,
~~ Maneesh

 

[SirJB7]

Hi, Maneesh Massey!

The exit event of a text box control is triggered when it loses the focus (i.e., as LostFocus in Visual Basic). So instead of setting again the focus by the SetFocus method that's only available in VBA for windows objects and not for controls, set the Cancel value to true and the control will retain the focus.

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.


Regards!

 

[Maneesh Massey]

Hi SirJB7,
Sorry, I did set Cancel=True on the Exit event of the Textbox, but still the cursor does not come back into the textbox.

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

Sorry, I did not understand any of the explanation you gave above.
Can you please help me Textbox validation on String entries, Numeric values and email addresses ?
Regards,
~~ Maneesh

 

[SirJB7]

Hi, Maneesh Massey!

Earlier I was leaving on a rush (still have to do Santa's job buying gifts) and maybe I sounded a bit cryptic, that wasn't the intention.

If I were you, I'd start here:
http://chandoo.org/wp/excel-vba/user-forms/
http://chandoo.org/wp/tag/data-validation/
http://chandoo.org/wp/2011/03/30/form-controls/
... for a general viewpoint of what can be done and how.

Later and specifically focusing on text box validations, I'd go thru here:
http://support.microsoft.com/kb/829070
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.textbox_events.aspx

Another that'd be useful and not widely seen:
http://forum.chandoo.org/threads/macro-to-prevent-user-to-paste-in-the-textbox.8120/

But if found the last ones a bit rough, you can do this:
- create a new workbook
- insert a user form
- insert a text box control
- double click on it to go to the click event code
- click on the top right drop down list for the available events, and write them down:
AfterUpdate *
BeforeDragOver
BeforeDropOrPaste
BeforeUpdate *
Change *
DblClick
DropButtonClick
Enter *
Error *
Exit *
KeyDown **
KeyPress **
KeyUp **
MouseDown
MouseMove
MouseUp

You can first consider only those which have asterisks, for data validation purposes. There're to different ways to validate the data entered in a text box:
a) When you finish inputting the data (*) (one asterisk), i.e. for example in the Exit event (similar in VBA to VB LostFocus), as in the following link:
http://stackoverflow.com/questions/19772214/excel-vba-textbox-events
b) While you're typing the data (**) (two asterisks), i.e., for example in the KeyDown event as in the following link:
http://social.msdn.microsoft.com/Fo...xcel-vba-codetextbox-enter-event?forum=isvvba

The ideal is a combination of the two methods to prevent unwanted characters while typing and making it easier to validate when losing focus, but you could use only this last (previous first) taking care of each type of validation that you require for each control:
- if numeric
- if numeric with sign
- if numeric with decimal places
- if date
- if mail
... etc.

Each type of validation will use a different test condition. Just start playing a bit with all this and in a couple of days come back and tell us how did you do.

Hope it helps.

Regards!