I have a userform I'm creating for work that will contain batch job information. I have 30 rows of textboxes, each row has a separate Job Order, Part Number, and Customer textbox. When the user enters a Job Order number in the first field, the JobOrder_Exit code fires, disabling the Part Number and Customer fields and then pulling in job information from our SQL server to autofill the Part Number and Customer textboxes. If there is no job order number in the system, then the job is a custom and the Part Number and Customer fields are enabled so the user can fill those with custom information.
I want to give the form the capability of detecting duplicate entries (there ARE 30 rows after all!). If the user enters a job order number, I want the code to be able to detect if that number has already been entered on the form and then display a msgbox stating this was a duplicate number.
Good so far! I've got that working, what DOESN'T work is that I want to then clear the textbox they just entered and then highlight the box that has the first instance of that duplicate job order number. I tried doing this with the .SetFocus method, but unfortunately using the .SetFocus necessarily removes the focus off the current textbox thereby re-firing the textbox_Exit code resulting in the duplicate textbox NOT being highlighted; the next textbox is selected as if there was no problem.
How do I highlight a different textbox FROM textbox_exit change event?
Here's the general code structure:
Private Sub Batch_Job01_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'I have this code for all 30 textbox_exit events
Call JobPull(Batch_Job01.Value, 1)
End Sub
Private Sub JobPull(Job, JobNum)
Dim JobField As Object
Dim DupPos As integer
Dim Result As Boolean
Call DuplicateJobCheck(JobNum, DupPos, Result)
'JobNum is an input, DupPos and Result are outputs.
'DupPos gives the job number of the duplicate job,
'and Result is either true or false depending on whether there is a duplicate.
If Result = False then
'code to pull in job information off our server
else
MsgBox ("Duplicate Job")
If DupPos <10 then Set Jobfield = Me.Controls("Batch_PartNumber0" & DupPos)
If DupPos > 9 then Set Jobfield = Me.Controls("Batch_PartNumber" & DupPos)
JobField.SetFocus '<<---THIS IS WHERE THE PROBLEM IS. I can't figure out how to KEEP the focus here after the _Exit event finishes firing.
End If
End Sub
Sub DuplicateJobCheck(JobNum, DupPos, Result)
'Dim and Set Form control variables
'For loop to test each Job field's contents
'If contents match, Result = True, DupPos = i
End Sub
Bookmarks