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

[kann]

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:

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

[skywriter]

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

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

[kann]

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.

[skywriter]

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.

[gmaxey]

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?


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???

[skywriter]

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.

[gmaxey]

skywriter,

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