Preventing incorrect data from being copied

[TimN]

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

 

[Rich J]

I'm not an expert but why don't you make the textbox for entering the ID
number into a combobox that selects from the list of employee numbers. That
way the user doesn't have the opportunity to enter a wrong number. The
combobox row source can be set in the property list or by VB. It can refer to
the list of #'s and names on the other sheet and put the value on the sheet
you are calling the userform from.

Set BoundColumn to 1, ColumnCount to 2, ColumnWidths to something like 50,
120.
Set the Rowsource to something like Sheet1!A1:B1000 and the Controlsource to
something like Sheet2!C5.
The value you select will be placed in C5.
You should probably use VB so that the value is updated before closing the
form.

 

[Rich J]

I played with some code to put in the userform. Change names accordingly

Private Sub UserForm_Initialize()
ComboBox1 = Range("C5") ' if the value exists when you
open form
TextBox1 = ComboBox1.Column(1)
End Sub

Private Sub ComboBox1_Change()

Range("C5") = ComboBox1.Value
TextBox1 = ComboBox1.Column(1) ' < this would be the second column of
data
' This also gives the employee name without the VLookUp
End Sub

 

[NickHK]

The option of only providing valid data is preferable, as Rich pointed out.
Bit if you can't do that for reason, only enable the OK button if the
textbox value is NOT "Invalid...".
e.g. following the code that checks the input and insets "Invali.." if
required:
Const INVALIDID As string="Invalid ID"
'Other code
cmdOK.Enabled=(txtEmplyeeID.Text<>INVALIDID)

NickHK

 

[TimN]

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.

 

[Rich J]

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]

Rich,

I understand now what you are saying and I like the idea. I implemented the
code as you suggested and I get a compile error Variable not defined on the
4th line of code below ...TextEmployeeNumber = ComboBox1.Column(1)
Here is my code with my specific names:

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

Private Sub ComboBox1_Change()
On Error Resume Next
Range("C3") = ComboBox1.Value
TextEmployeeNumber = ComboBox1.Column(1)
End Sub

What am I doing wrong??

 

[Rich J]

Where I had Textbox1 it was to show the name associated with the employee #.
Rename that to the name of the textbox on the form that has the Employee
Name. The combobox has two columns. Employee # & Employee Name. It looks
like you are adding the combobox and then trying to assign your existing
textbox (TextEmployeeNumber) the value of the combobox. Combobox1.Value is
whatever employee # is typed in the combobox or selected. The expression
=ComboBox1.Column(1) is actually the second field which is the employee name.
It should be the name of the textbox where the name is being shown. Don't
put anything in the ControlSource in the Properties, the VB code puts the
value in the cell. If you need to put the name in a cell next to the number
then use Range("D3") = ComboBox1.Column(1).

And, if TextEmployeeNumber is not the name of the existing textbox, VB is
thinking you created a variable name. You said you had three textboxes. I
misread and I thought one was employee #, one was employee name and a third
was date.
You could add another textbox on the form and the employee name will show
right there.

 

[TimN]

Rich,

I will give it another try. I am very new to VBA, so I am just trying to
find my way through this stuff. I appreciate your help.

TimN