if they start typing, the number will fill in automatically so you would have
the best of both.

I put error check in and seems to work well typing in the number.

in module attached to whatever control calls your userform
Sub EmployeeForm()
On Error Resume Next
UserForm1.Show
End Sub

in userform code

Private Sub UserForm_Initialize()
If Not IsEmpty(Range("C5")) Then
ComboBox1 = Range("C5")
TextBox1 = ComboBox1.Column(1)
End If
End Sub

Private Sub ComboBox1_Change()
On Error Resume Next
Range("C5") = ComboBox1.Value
TextBox1 = ComboBox1.Column(1)

End Sub


"TimN" wrote:

Thanks for the ideas. I had thought about the list box. My hesitation is
there are 1,800 employees and all I am interested are those who are out on
Short Term Disability, maybe just 10 or so at any given time. And further,
this tool would be used by a department admin. who would typically only have
one or maybe two people out from his/her department at any given time. It
would be a big list to look through to find one or two people.

Might still be a good option though. Thanks again for the help.


"TimN" wrote:

I have a user form that has three text boxes along with an OK command and a
Cancel command. The first textbox is an employee ID number, the next two are
dates. The Employee ID is entered and then a VLOOKUP formula goes to another
worksheet and finds the employee name and other data. If it doesn't find a
name I have written into the formula to enter "Invalid Employee Number" in
the cell.

Here is the situation:

If an invalid employee number is entered and the user doesn't notice bofore
clicking OK, the "invalid" data is copied to another hidden worksheet and it
is just a sheet of errors since the employee number wasn't valid.
OR
If the user enters a valid number, but doesn't notice until he/she hits OK
that the wrong employee number was entered, then that wrong employee data is
copied to the hidden worksheet.

What is the best way to prevent either of these from getting copied before
the user is certain the correct employee number is entered?

I attempted to create a MsgBox at the first Input box asking "Is this the
Correct Employee Number?". However the message box appeared after a single
digit was entered. For example if the employee number is 1554, the message
box popped up when you type 1, not at the end of typing 1554.

What do you experts suggest????