'Userform - Redirect focus on textbox after MsgBox

I hope this is not that stupid, but I really did not find a post that was working for me. Situation: I want someone to put a date into a textbox in a Userform.
ErrorHandler: I wanted to have a very simple solution if the user doesn't enter the right format. (EB_Start.Activate and EB_Start.SetFocus are NOT working at all)

For this I got:

Private Sub EB_Ende_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   On Error GoTo Error_Handler
   Me.EB_Ende = CDate(Me.EB_Ende)                                                  
      Error_Handler:
      EB_Start.Activate
      EB_Start.SetFocus
MsgBox ("Please enter a valid date"), , "Datum"
End Sub

Problem: My Question is now, how do I redirect the focus on the textbox(EB_Ende) The current reaction is, after the user presses Enter after the MsgBox showed up, It continued to the next textbox, but I want the user to be forced to reenter a valid date in the textbox.

If someone could help me out with this, or redirect me to a Post or link that will answer my question I would really appreciate it.

Best regards, Lutscha

This is the whole UserForm

Solution 1:^[1]

Setting focus in BeforeUpdate event won't work - it is too soon. It is fired before focus is moved to the next control. Better approach is to handle Exit event and cancel it when needed:

Private Sub EB_Ende_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  On Error GoTo Error_Handler
  EB_Ende = CDate(EB_Ende)
  Exit Sub   '<-- exit sub when there is no error
Error_Handler:
  Cancel = True
  MsgBox ("Please enter a valid date"), , "Datum"
End Sub

Solution 2:^[2]

There are a couple of issues with your code (e.g. not exiting before the error handler, and do you want to use Cancel=msoTrue to cancel the text entry to EB_Ende when you get an error?) so you could try this:

Private Sub EB_Ende_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   On Error GoTo Error_Handler
   Me.EB_Ende = CDate(Me.EB_Ende)                                                  
   Exit Sub
Error_Handler:
    EB_Start.SetFocus
    MsgBox ("Please enter a valid date"), , "Datum"
End Sub

Or, you could skip the error-hander entirely:

Private Sub EB_Ende_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.EB_Ende) Then
        Me.EB_Ende = CDate(Me.EB_Ende)                                                  
    Else
        Cancel = msoTrue
        EB_Start.SetFocus
        MsgBox ("Please enter a valid date"), , "Datum"
    End If
End Sub

Solution 3:^[3]

The funny thing is, you used the answer as a tag on your question:

Me.EB_Ende.SetFocus

Here is more information on SetFocus.

Something else that might help... you can change the default order that objects are "tabbed" through. (ie., what cell should get focus after you press enter or tab in the current control:

Solution 4:^[4]

worked perfect

Private Sub txtSalary_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsNumeric(txtSalary) Then MsgBox "Please, only numbers!", vbRetryCancel Cancel = True txtSalary = "" End If

If IsNumeric(txtSalary) Then
txtSalary = Format(txtSalary, "$#,##0.00") End If End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	BrakNicku
Solution 2	Chronocidal
Solution 3
Solution 4	JCarlos

'Userform - Redirect focus on textbox after MsgBox

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]

Solution 4:^[4]