How to set focus back into the textbox after an error message?

I have a user form with several textboxes and when user inputs data , it gets stored on excel sheet. I am having trouble setting the cursor back into the textbox after an error message, while I am doing the data validation in my last command button called save. Although, the problem does not seem to appear, while I am doing the data validation in the exit events of the individual text boxes. The code is as seen below.

Code:

Code:

---

Private Sub CmdSave1_Click()

 Dim row As Long
  Dim c As Range
 row = ActiveCell.row
 
 
  For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
  'Call txt_BPName1_Exit(c)
 
            If c.Value = txt_BPName1 Then
                MsgBox " Duplicate Found.Please enter unique Base Product"
                txt_BPName1.SetFocus  '>>> the cursor does not return to textbox here.
                'cancel = True
                End If
                Next
                txt_SPName1.SetFocus
                'Exit Sub
           
                For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).row)
               
           
        If c.Value = txt_SPName1 Then
          MsgBox "Cell " & c.Address & " Duplicate sub Product Found."
              'txt_SPName1.SetFocus
              cancel = True
             
              End If
              Next
              txt_loc1.SetFocus
              'Exit Sub

---

I have attached the file here for reference. please check out Add_InventoryBP Userform for full code.

Thanks,
Kann

It works for me if you exit the procedure after setting focus, which is what you want to do, right?

Code:

---

  For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)

  'Call txt_BPName1_Exit(c)
 
            If c.Value = txt_BPName1 Then
                MsgBox " Duplicate Found.Please enter unique Base Product"
                txt_BPName1.SetFocus  '>>> the cursor does not return to textbox here.
                Exit Sub
            End If
  Next c

---

Exit sub does bring back the cursor to the textbox. But, I don't want to exit the sub at that point, because I have other lines of code that I need the program to do before it should exit. Is there a substitute for exit sub ?. or Even if you have to exit out of a sub, is there a way to enter back again.?

I had originally the data validation happening within the exit events of each textbox. But, now need to make it happen within the save button.

I don't know how to stop any code and have it start over again after you've entered a value in a user form text box.

I think this is why most people use the exit event because it runs automatically and the user just tries again, no buttons to push after fixing the problem.

I guess you could have an input box popup asking you for a new value and then you could have a jump to point to take you back before the loop and make it start all over again, but I don't know how to get you back to the form to enter a value there and have the loop just start over. I would say assign a variable for what's in the textbox that way you can change that variable with the input box value if a duplicate is found. Maybe you could assign the input box value using the name of the text box I don't know, I would have to test it to find out.

Kann,

I'm not an Excel expert, but what you are saying really makes no sense. Look at your code. Why would you want to tell a user he or she as entered and invalid value and then continue an write that invalid value the sheet?

Code:

---

Dim bInvalid As Boolean
 bInvalid = False
  For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
    If c.Value = txt_BPName1 Then
      MsgBox " Duplicate Found.Please enter unique Base Product"
      txt_BPName1.SetFocus
      bInvalid = True
      Exit For
    End If
  Next
  If Not bInvalid Then 'What is the point of running this code if you have already set the focus to txt_BPName1?
    For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).row)
      If c.Value = txt_SPName1 Then
        MsgBox "Cell " & c.Address & " Duplicate sub Product Found."
        txt_SPName1.SetFocus
        bInvalid = True
        Exit For
      End If
    Next
  End If
 
  'So even if the values in the textboxes validatated abuve are "Invalid",  you still want to
  'write them to your sheet???

---

That's not what I get out of it. He wants to be able to fix the issue by being directed back to the form where the user would enter a new value and you would somehow be put right back into the code without having to hit the save button again. My solution which I personally don't like, is to have an input box come up ask for a new value and use a goto to re-enter the for loop with the new value.

skywriter,

Personally, I would disable the Save command button until the conditions for its successful execution was right.