How to set focus of my cursor from exit event of one textbox to another textbox

Question

I have a userform with two textboxes BPName2 and SPName2. After the If found=true then exit for statement in below code. I would like to move to the 2nd textbox. But, having trouble doing so. Anyone have an idea on how to do this?

Private Sub txt_BPName2_Exit(ByVal Cancel As ReturnBoolean)

Dim Row As Integer
  Row = ActiveCell.Row
  Dim c As Range
  Dim found As Boolean


  found = False
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If c.Value = txt_BPName2 Then MsgBox "Cell " & c.Address & " Base Product Found."
        If c.Value = txt_BPName2 Then found = True
    If found = True Then Exit For

    Next

    If found = False Then
         'Cells(Row, 1).Value = txt_BPName2.Text
         MsgBox ("Base Product not found")
         'ActiveCell.Offset(1, 0).Select
         Add_Inventory_SP.Hide
    End If

    Cancel = True
End Sub

Comment out the line with `MsgBox`. Does that do what you want? — Ken White, Apr 14 '16 at 02:51
Like `NextTextBoxName.SetFocus`? Also your code can be modified to be more efficient. — PatricK, Apr 14 '16 at 02:58
Is `SPName2` the next control in the tab order? If so, then just don't set `Cancel = True` for that circumstance. If not, you're likely fighting a losing battle since any `SetFocus` command will be followed by the buffered tab that raised the exit event. — Rory, Apr 14 '16 at 07:13

score 2 · Answer 1 · edited May 23 '17 at 11:59

Couple of things

Whenever you are working with Excel Rows, please use Long instead of Integer. Post Excel 2007, the number of rows have gone up to 1048576 which an Integer cannot handle
You do not need a Boolean Variable
Fully qualify your objects. Avoid using Activecell. You may want to see this
Avoid using reserved words as variable names. Row is reserved word.

Is this what you are trying? (Untested)

Private Sub txt_BPName2_Exit(ByVal Cancel As ReturnBoolean)
    Dim ws As Worksheet
    Dim lRow As Long
    Dim c As Range, rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1") '<~~ Change as applicable

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("A2:A" & lRow)
    End With

    For Each c In rng
        If c.Value = txt_BPName2 Then
            MsgBox "Cell " & c.Address & " Base Product Found."

            SPName2.SetFocus
            Exit Sub
        End If
    Next

    MsgBox ("Base Product not found")
    Add_Inventory_SP.Hide
    Cancel = True
End Sub

Note: A more efficient approach in lieu of looping and searching would be to use .Find or Application.Worksheetfunction.CountIf to search for the name in the range. If you are interested in .Find then you can see This

@ Siddharth Rout- Thanks ! Your approach is much better. – Abhi0609 Apr 18 '16 at 16:17 — Abhi0609, Apr 18 '16 at 16:17

How to set focus of my cursor from exit event of one textbox to another textbox

1 Answers1