MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Textbox exit problem


Posted by Beginner Bob on November 15, 2001 12:30 PM

Can somebody help me with the textbox_exit routine?

Here's my current code:

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = "" Then
MsgBox "A LOCATION MUST BE ENTERED."
End If
End Sub

How do I bypass this if the Cancel button (which unloads the userform) is pressed? I want to insure that the user inputs something into textbox6 unless they want to cancel out of the form. Currently, the message box comes up even if cancel is pressed. In addition, I want to make sure that the focus goes back into textbox6 if tab is pressed etc. when it's left blank. Thanks for any help in advance.


Posted by Rick E on November 15, 2001 1:16 PM

Add a line to your Exit code

Add a SendKeys line as shown to send the curser back to the textbox.

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = "" Then
MsgBox "A LOCATION MUST BE ENTERED."
SendKeys "+{TAB}" ' This goes back to TextBox6
End If
End Sub

As for the Cancel button problem, if you are in TextBox6 and you click the Cancel button, you are exiting the textbox which means the exit macro code for TextBox6 will be executed. Maybe the check for textbox6 should be put in the next focused object, (TextBox7 ?) on the Enter macro. The SendKeys will send it back to TextBox6 after the msgbox. I usually put that kind of check on the "OK" or "UPDATE" button and set the focus to the object that needs the information.

Posted by rICK e on November 15, 2001 1:26 PM

TAB back with code

Add a line of code to tab back to the textbox as:

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = "" Then
MsgBox "A LOCATION MUST BE ENTERED."
SendKeys "+{TAB}"
End If
End Sub

As for the Cancel button problem, when you click the button, you are exiting fron that text box and so the exit code is executed. One solution is to move that code to the next object (TextBox7 ?) on the Enter funtion. I usually add this kind of check at the "UPDATE" button, check all required fields and set the focus to the textbox that is missing data. Hope this helps. Rick E

Posted by Rick E on November 15, 2001 1:28 PM

Re: TAB back with code


Posted by Beginner Bob on November 16, 2001 12:06 PM

I added a check macro called by the Next and OK buttons


Via Vlookup formula you cant lookup data must be visable (OPEN BOOKS), i understand that VBA can look in closed SS but thast beyond me, lets hope a programmer catches this post.