User form Cancel button not working when focus in a textbox that fails validation

[DavidPC]

I have a UserForm with a number of textbooks plus OK and CANCEL buttons. One Textbox (for Age) gets validated in the Age_Exit Sub. If no age is entered, or if too high, then a MsgBox is displayed showing the error, Cancel is set True and the sub exited. This returns focus to the Age TextBox for the user to enter a valid age. My problem is that until the user has entered a valid Age then clicking on the CANCEL button has no effect, focus stays on the Age textbox.
I have tried using _BeforeUpdate but this does not solve the problem.
Is it possible to get round this other than by telling the user to enter a valid age then click on CANCEL?
Thanks for any ideas.

 

[ZVI]

Hi,
Try this workaround:

Option Explicit
' Put on form controls: Age textbox and Cancel button
 
Dim IsCancel As Boolean
 
Private Sub Age_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If IsCancel Then Exit Sub
  ' Check an Age (change to suit)
  Cancel = Age < 18 Or Age > 65
  If Cancel Then MsgBox "Valid range for Age: 18...65", vbExclamation, "Wrong age!"
End Sub
 
Private Sub Age_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  ' Esc is pressed, Cancel button is allowed from Age textbox
  IsCancel = KeyCode = 27
End Sub
 
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ' Exit of Age textbox is under control
  IsCancel = False
End Sub
 
Private Sub Cancel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ' Click on Cancel button is allowed from Age textbox
  IsCancel = True
End Sub
 
Private Sub Cancel_Click()
  Unload Me
End Sub

More correct way is in checking age in code of Ok/Apply button.
Regards

 

[DavidPC]

Hi,
Try this workaround:

Option Explicit
' Put on form controls: Age textbox and Cancel button
 
Dim IsCancel As Boolean
 
Private Sub Age_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If IsCancel Then Exit Sub
  ' Check an Age (change to suit)
  Cancel = Age < 18 Or Age > 65
  If Cancel Then MsgBox "Valid range for Age: 18...65", vbExclamation, "Wrong age!"
End Sub
 
Private Sub Age_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  ' Esc is pressed, Cancel button is allowed from Age textbox
  IsCancel = KeyCode = 27
End Sub
 
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ' Exit of Age textbox is under control
  IsCancel = False
End Sub
 
Private Sub Cancel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ' Click on Cancel button is allowed from Age textbox
  IsCancel = True
End Sub
 
Private Sub Cancel_Click()
  Unload Me
End Sub

More correct way is in checking age in code of Ok/Apply button.
Regards

Many Thanks, this works well for me. I have not used the Age_KeyDown or UserForm_MouseMove Subs, it seems to do what I need without these. I have also set the IsCancel to False in the Cancel_MouseMove Sub since after the CANCEL button is clicked i just clear the form contents rather than Unloading it.

The reason i am checking age when it is entered rather than in the code of the OK button is that it affects later items on the form and I think it will be faster for the user to know of his/her error as soon as it is made. I have other TextBoxes on my UserForm that I need to validate on input and I will use your method for these also now.

Thanks again for your great help

David