SET FOCUS to Original userform text field

[chipsworld]

Hey everyone...need some guidance.
I am working on a simple userform to input data to a sheet, but am trying to eliminate duplicates...

The idea in the below code is that if the number already exists, the user gets a pop saying that the number has already been entered and then I want to set the focus back to the first text box (txtanum). Currently I can not stop it from tabbing to the second text box...how can I achieve this?

Here is the afterupdate code for the first test box on the userform. I have tried this in the "exit" field as well...always moves to the second text field....

Private Sub txtanum_AfterUpdate()
Dim ws As Worksheet

Set ws = Sheets("dBase")

If Me.txtanum.Text > "" Then
    With ws
    Dim Rw As Long, Fnd As Range
            lkup = Me.txtanum.Value
        Set Fnd = .Range("A:A").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
            If Not Fnd Is Nothing Then
        MsgBox "That 'A' number has already been entered into the system." & vbCrLf _
        & "Please use the 'Record Update' form to add or correct data.", vbOKOnly
        txtanum.Text = ""
        txtanum.SetFocus
        End If
    End With

   
End If

End Sub

 

[DanteAmor]

Try exit event:

Private Sub txtanum_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim ws As Worksheet
  Dim Fnd As Range
  
  Set ws = Sheets("dBase")
  With Me.txtanum
    If .Value <> "" Then
      Set Fnd = ws.Range("A:A").Find(.Value, , xlValues, xlWhole, , , False)
      If Not Fnd Is Nothing Then
        MsgBox "That 'A' number has already been entered into the system." & vbCrLf _
          & "Please use the 'Record Update' form to add or correct data.", vbOKOnly
        .Value = ""
        Cancel = True
      End If
    End If
  End With
End Sub

 

[EXCEL MAX]

Try to use this => SendKeys "+{TAB}" instead => txtanum.SetFocus

 

[Jon Peltier]

It turns out that control doesn't go to the next textbox (on Tab) until after the _AfterUpdate routine is finished, so any .SetFocus you do has no effect.

But I did the following, with a simple UserForm with two textboxes (1 and 2) and a module-level string variable that indicates which control should get focus (if the variable is "", then nothing unusual happens).

Module code:

Option Explicit

Private NextControl As String

Private Sub TextBox1_AfterUpdate()
  NextControl = ""
  With Me.TextBox1
    If .Text = "go back" Then
      NextControl = "TextBox1"
      .Text = vbNullString
    End If
  End With
End Sub

Private Sub TextBox2_Enter()
  If Len(NextControl) Then
    Me.Controls(NextControl).SetFocus
  End If
End Sub

 

[chipsworld]

Try exit event:

Private Sub txtanum_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim ws As Worksheet
  Dim Fnd As Range
 
  Set ws = Sheets("dBase")
  With Me.txtanum
    If .Value <> "" Then
      Set Fnd = ws.Range("A:A").Find(.Value, , xlValues, xlWhole, , , False)
      If Not Fnd Is Nothing Then
        MsgBox "That 'A' number has already been entered into the system." & vbCrLf _
          & "Please use the 'Record Update' form to add or correct data.", vbOKOnly
        .Value = ""
        Cancel = True
      End If
    End If
  End With
End Sub

Works perfectly! Thank you so very much. I just couldn't figure this one out for anything! LOL

 

[DanteAmor]

Im glad to help you, thanks for the feedback.