Set Focus Not Working

[Denny57]

I am attempting to create a simple form to input and search for details using date records. I am aware of the complexities or using date formats when using text boxes and the DTPicker poses additional problems when I try to use VLOOKUP to search a table where dates are the key.

To avoid this I have created the dates in the lookup table in text format (dd/mm/yyyy).

I have created simple code which works as I want when the date in entered into the txtDate textbox in the required format. The sub-routine code auto tabs to the next field and the correct information is gathered from the look up table.

I have a message box that appears when the details input into the txtDate is not in the correct format and have limited this box to 10 characters. However, when incorrectly formatted information is input into the txtDate box I want this field to clear AND the cursor reappear in this textbox when I click on the Message Box. Unfortunately, the cursor appears in the next text box.

Private Sub txtDate_AfterUpdate()
    
    If Len(Me.txtDate.Text) <> 10 Then
        MsgBox ("Input must be in the format dd/mm/yyyy")
        txtDate.Value = ""
        txtDate.SetFocus
        Exit Sub
    End If
    
    With Me
    
    .txtPayMonth = Application.WorksheetFunction.VLookup(Me.txtDate, Sheet2.Range("A2:B370"), 2, False)
    End With
       
End Sub

 

[RoryA]

If you use the BeforeUpdate event, you can just use Cancel = True instead of trying to set focus.

 

[Denny57]

If you use the BeforeUpdate event, you can just use Cancel = True instead of trying to set focus.

Sorry Rory
Please ignore my inexperience here but I created a BeforeUpdate Sub-routine which is not recognised by VBA

Private Sub txtDate_BeforeUpdate()
    If Len(Me.txtDate.Text) <> 10 Then
        MsgBox ("Input must be in the format dd/mm/yyyy")
        txtDate.Value = ""
        'txtDate.SetFocus
        Cancel = True
        Exit Sub
    End If
End Sub

 

[Denny57]

I am attempting to create a simple form to input and search for details using date records. I am aware of the complexities or using date formats when using text boxes and the DTPicker poses additional problems when I try to use VLOOKUP to search a table where dates are the key.

To avoid this I have created the dates in the lookup table in text format (dd/mm/yyyy).

I have created simple code which works as I want when the date in entered into the txtDate textbox in the required format. The sub-routine code auto tabs to the next field and the correct information is gathered from the look up table.

I have a message box that appears when the details input into the txtDate is not in the correct format and have limited this box to 10 characters. However, when incorrectly formatted information is input into the txtDate box I want this field to clear AND the cursor reappear in this textbox when I click on the Message Box. Unfortunately, the cursor appears in the next text box.

Private Sub txtDate_AfterUpdate()
  
    If Len(Me.txtDate.Text) <> 10 Then
        MsgBox ("Input must be in the format dd/mm/yyyy")
        txtDate.Value = ""
        txtDate.SetFocus
        Exit Sub
    End If
  
    With Me
  
    .txtPayMonth = Application.WorksheetFunction.VLookup(Me.txtDate, Sheet2.Range("A2:B370"), 2, False)
    End With
     
End Sub

Resolved after some investigation

Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.txtDate.Text) <> 10 Then
        MsgBox ("Input must be in the format dd/mm/yyyy")
        txtDate.Value = ""
        'txtDate.SetFocus
        Cancel = True
        Exit Sub
    End If
End Sub