Userform textbox validation - private sub doesn't fire on tab exit

[mhessnm]

Hello,

I have a problem that probably won't matter at all but it is bothering me and I'm hoping someone can help. I"m a novice at VBA, but understand enough to adapt what I find and make it work for me, mostly...so I'm thankful for any help I get both with my VBA education and the effectiveness of my efforts.

I have a userform, image below:

As you can see, the form has some initial input that stays at top all the time, and then separate pages with different fields that you can enter additional data into. I have put in place some data validation routines for dates, numeric fields etc. One thing I wanted to do was to guard against entering non-numeric data in the fields at right. On three of the fields, the private sub textbox_exit routines work fine, and a messagebox pops up if you enter something non-numeric no matter whether you tab or click out of the box - clicking okay in the message box sends the user back to the textbox to correct. On the last one, Therapy, however, it does not. Clicking out of the box will bring up the message if you click within the page (but not if you change pages) and tabbing sends the cursor up to the top of the form at Entry Date and will not bring the message box unless you tab all the way through the top of the form and it comes back to the page - the tab on Therapy does not seem to fire the exit event. I don't understand the tab order - while I've set the tab order on the page starting with 0 I don't understand why it doesn't go back to index 0 (Case Management) after tabbing out of the Therapy field and instead goes all the way up to Entry Date (tab index 0 on the main userform) and wonder if that is part of the problem.

I tried doing a multipage change event, and that works if you change the page but does not solve the tab issue, and I ran into another problem - if I change the page and the box comes up, and I use the code MultiPage1.Value = 2 to get back to the correct page, it just shows that the page is selected but the fields don't change to the previous page fields - it appears page 2 gets selected but the fields remain page 3 fields.

All I really want to do is have the exit event fire on the Therapy textbox when tabbing or clicking out. And as a bonus further check, if possible, have a message box trigger if the page is changed and the entry is not numeric and come back to focus on the Therapy textbox.

Here is the code for textbox field exit:

Private Sub txtTherapy_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(txtTherapy) Then
If txtTherapy.Value = "" Then
Exit Sub
Else
txtTherapy.Value = Null
MsgBox "Please enter a valid number in the Therapy field!", vbCritical, "Invalid Entry!"
Cancel = True
End If
End If
End Sub

And here is the code for the MultiPage change event:

Private Sub MultiPage1_Change()
If txtTherapy.Value = "" Then
Exit Sub
Else
txtTherapy.Value = Null
MsgBox "Please enter a valid number in the Therapy field!", vbCritical, "Invalid Entry!"
Me.MultiPage1.Value = 2
Me.txtTherapy.SetFocus
Cancel = True
End If
End Sub

 

[mhessnm]

I should also add that the line to setfocus on the textbox after the multipage change leads to an error, saying it is unable to setfocus on the Therapy field because it is invisible, or not present. So I've had to comment that out for now.

 

[mikerickson]

To avoid your problem, you could validate as the user enters.

This routine will allow the user to only enter numbers (positive integers) into TextBox1. (More complicated routines would be needed to include negative integers or fractional numbers.)

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not Chr(KeyAscii) Like "[0-9]" Then
        KeyAscii = 0
        Beep
    End If
End Sub

 

[mhessnm]

Cool! I'll give it a try...and they are only doing positive integers anyway, so if it works it will be fine. Thanks, will report back!

To avoid your problem, you could validate as the user enters.

This routine will allow the user to only enter numbers (positive integers) into TextBox1. (More complicated routines would be needed to include negative integers or fractional numbers.)

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not Chr(KeyAscii) Like "[0-9]" Then
        KeyAscii = 0
        Beep
    End If
End Sub

 

[mikerickson]

Note that you will need that routine for every textbox that needs validation. (If you are doing a dozen or more, a Class module....)

 

[mhessnm]

I don't think I have that many, but I will have to look that up - I'm not familiar with Class modules, but it will be a good learning experience for me.

Note that you will need that routine for every textbox that needs validation. (If you are doing a dozen or more, a Class module....)

 

[mhessnm]

Hi mikerickson - this worked perfectly. Thank you for the help! I'm going to try to learn about Class modules.

I was thinking of purchasing a book on VBA programming in Excel. I know enough to be dangerous, but not enough to avoid patching things together. I've heard good things about Walkenbach's book. Do you have a suggestion?

This can be marked closed! Thanks!

 

[mikerickson]

Walkenbach's is the one that I learned from. Not so much class modules, but its a good book for Excel VBA.

 

[mhessnm]

Walkenbach's is the one that I learned from. Not so much class modules, but its a good book for Excel VBA.

Thanks! I appreciate it!