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

DavidPC

New Member
Joined
Apr 23, 2019
Messages
8
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi,
Try this workaround:
Rich (BB code):
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
 
Last edited:

DavidPC

New Member
Joined
Apr 23, 2019
Messages
8
Hi,
Try this workaround:
Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,357
Members
417,139
Latest member
bdmprasenjit

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