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

mhessnm

New Member
Joined
Apr 12, 2019
Messages
25
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:

1588020646128.png


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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mhessnm

New Member
Joined
Apr 12, 2019
Messages
25
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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.)

VBA Code:
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

New Member
Joined
Apr 12, 2019
Messages
25
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.)

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

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

New Member
Joined
Apr 12, 2019
Messages
25
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

New Member
Joined
Apr 12, 2019
Messages
25

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Walkenbach's is the one that I learned from. Not so much class modules, but its a good book for Excel VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top