vbaexceluserformsetfocus

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 Sry it is in german


Solution

  • 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